logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Modifying Excel Chart Colors Based on Percentage Values in Different Worksheet

elfel 5652 18
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Reply | New topic
  • #1 16564151
    elfel
    Level 7  
    Hello,
    i have a worksheet where there are 3 charts: two pie and one bar (5 bars), the chart has% values. Data for the chart is drawn from another sheet. I want the color to change depending on the% value. Help please
  • ADVERTISEMENT
  • #2 16564372
    JRV
    VBA, Excel specialist  
    Attach the file and specify the Excel version
  • ADVERTISEMENT
  • #4 16564792
    JRV
    VBA, Excel specialist  
    Honestly I was hoping along with the files to explain what color and in which frames should be changed
    elfel wrote:
    the color changed depending on the% value
    When you reach a certain percentage? There are several stairs?

    Added after 8 [minutes]:

    how conditional formatting?
  • #5 16564892
    elfel
    Level 7  
    yes, exactly like in conditional formatting in the BASE sheet
  • #6 16564924
    JRV
    VBA, Excel specialist  
    the difficulty is that in conditional formatting the ColorIndex is used, but the charts tend to RGB (), i.e. the colors will not match ...

    Added after 1 [hours] 13 [minutes]:

    these percentages change by formulas?

    Added after 10 [minutes]:

    elfel wrote:
    are drawn from another sheet
    then run the macro, e.g. for Chart 1
    Code: text
    Log in, to see the code
    Integral function
    Code: text
    Log in, to see the code
  • #7 16565531
    elfel
    Level 7  
    Thank you for your help. I just have a problem with running this code. where should I paste this macro? because I have an error and nothing wants to happen :(

    Added after 12 [minutes]:

    screen in the attachment
    Attachments:
  • #8 16565560
    JRV
    VBA, Excel specialist  
    elfel wrote:
    because I have an error
    How exactly, the message? which line is highlighted?
  • ADVERTISEMENT
  • Helpful post
    #10 16565628
    JRV
    VBA, Excel specialist  
    post # 6,
    2 codes - procedure and function
    add features

    Added after 59 [seconds]:

    it's all in a modula code, not a sheet
  • #11 16565724
    elfel
    Level 7  
    revelation. it works
    thank you

    Added after 8 [hours] 38 [minutes]:

    I am struggling with one more topic :)
    i have about 30 different files. I need to collect in one sheet the data from cells from the same four columns (A1: A71; H1: H71; K1: K71; R1: R71) which are repeated in each of these files, preferably keeping the source formatting. Will it work?
  • #12 16566245
    JRV
    VBA, Excel specialist  
    At a time when the rockets are launched to the moon and beyond, they must be able to :)
    The only question is whether the files (30 different) are in the same folder and where to put the data. Rewrite on the page (bottom), overwrite?
  • #13 16566272
    elfel
    Level 7  
    The files are in one folder, the data can be dumped into one excel sheet and I would like them to be one under the other so that I could easily carry out some summaries
  • #14 16566353
    JRV
    VBA, Excel specialist  
    e.g. yes
    Code: text
    Log in, to see the code
  • #15 16566505
    elfel
    Level 7  
    extra :) thank you
    and you could also point him to a specific sheet in these files, from which he should draw data?
  • #16 16566514
    JRV
    VBA, Excel specialist  
    after opening
    sheets ("sheet_name"). Activate

    Added after 1 [minutes]:

    or Sheets ("name"). Range ("a1 ......."). copy
  • #17 16566566
    elfel
    Level 7  
    ok I will try
    and can you tell me what formula should I use to calculate the number of cells with the number from column A, but only if there is also a number in column B?
    in this case, the result should be 2
    Attachments:
  • Helpful post
    #18 16566615
    JRV
    VBA, Excel specialist  
    array formula (Ctrl + Shift + Enter)
    = SUM (ISNUMBER (A: A) * ISNUMBER (B: B))

    Added after 2 [minutes]:

    or
    = SUMPRODUCT (ISNUMBER (A: A) * 1, ISNUMBER (B: B) * 1)
  • ADVERTISEMENT
  • #19 16566638
    elfel
    Level 7  
    thanks
    you are a sweeper :)
Reply | New topic
Report a violation of the law

Topic summary

The discussion revolves around modifying Excel chart colors based on percentage values derived from another worksheet. The user seeks assistance in changing the colors of two pie charts and one bar chart according to specific percentage thresholds, similar to conditional formatting. Responses include requests for the Excel file and version (Excel 2016), explanations of the challenges with using ColorIndex versus RGB for chart colors, and a provided macro code to automate the color changes based on percentage values. The user successfully implements the macro after clarification on where to paste the code. Additional queries arise regarding data consolidation from multiple files and calculating cell counts based on conditions.
Summary generated by the language model.
ADVERTISEMENT