• 413K Members
• 6,798 Online
• 473K Conversations
SOLVED

# Re: sum by color when colors are set by conditional formatting

Dear Sir, i have been trying to count the number of cells having two separate conditional formatted colours in a leave planer calendar. However after applying the VBA code that have been given by you i am getting the result as only 1 in my target cell. i have atfached the file, hoping for your help

# Re: sum by color when colors are set by conditional formatting

The range you used in your UDF and the the range of conditional formatted cells are not matching, that is why it do not work.

please read my earlier post for other users with similar issues.

# Re: sum by color when colors are set by conditional formatting

it is difficult to tell what could cause the return of zero. If you upload a dummy sample, I can look.

# Re: sum by color when colors are set by conditional formatting

Thank you for sharing your expertise about VBA Code using Conditional Formatting. I am not knowledgeable about the VBA Codes and Conditional Formatting Statements. Sir, I have made this worksheet after searching for VBA Codes online and applying them as per my requirements.  I  make a rather large worksheet (Range A1 : AK3000) every month wherein lot of calculations are there in Column Range J1:AJ3000. I have used colors for fonts in some cells and then used VBA code "SumByColor" (You can see the VBA Code in Module 4) to get the totals in the relevant cells at the end of the sheet. Sir I am attaching .xlsb sample file named "Master Worksheet" for your reference. I need help regarding Conditional Formatting and then using VBA Code for summing up the cells based on font colors.

My reference in the worksheet here is specially for cells U2, V2, W2, X2 and then with reference to these cells for cells AG3, AH3 and AI3. Currently the sum of cell range W2:W17 is done by "SumByColor" VBA Code.

The color of cells W2, W4, W6, W12 and W15 is based upon the value of Tax Percentage in cells X2, X4, X6, X12 and X15 -for which I am using an IF Statement (=IF(V2<=2499.49,"12%","18%"),

The value of X2 changes to 12% if the room tariff is:

• Less than or equal to 2499.49 : 12 %

The value of X2 changes to 18% if the room tariff is:

• Greater than or equal to 2499.5: 18 %

At present I am changing the color of the cells manually which is becoming a very tedious task. I have set the basic font color of all the cells W2, W4, W6, W12 and W15 as “Blue” and then if the value of cell X2 is 12%, I manually set the font color of cell W2 to Red.

The total of cell range W2:W17 gives the result in cell H18 (Total Basic Amount Room Revenue (Base Tariff for 12% GST)  for all the cells with Red Color Font and The total of cell range W2:W17 gives the result in cell H21 for all the cells with Blue Color Font (Total Basic Amount Room Revenue (Base Tariff for 18% GST).

Sir, I tried using conditional formatting to do the above as you have explained with examples, but when I use the “SumConditionColorCells” all I am getting is #NAME? or NO COLOR. Sir please tell me how to do it correctly so that I can get the sum of all the cells with Red Color Font in respective cell and also the sum of all the cells with Blue Color Font in the respective cell.

Similarly, the color of cells AG3, AH3 and AI3 is dependent on the value of cells X2 and the color of cells AG4, AH4 and AI4 is dependent on the value of cell X4. Kindly help me with the conditional formatting formula and the respective VBA code for these cells too.

The font color of cells AG3 and AH3 changes to Red, Accent 2, Darker 50% if the value of X2 is 12% and the font color of AG3 and AH3 changes to Red if the value of X2 is 12%.

The color of cells AG3 and AH3 changes to “RGB(217,151,149)” if the value of X2 is 18% and the color of cells AG3 and AH3 changes to Blue Color Font if the value of X2 is 18%.

The sum total of Cell AG2:AG17 goes to output cell “H32” if the value is for 18% Tax (Blue) and goes to output cell”H34” if the value is for 12% Tax (Red)

The sum total of Cell AH2:AH17 goes to output cell “H33” if the value is for 18% Tax (Blue)  and goes to output cell”H35” if the value is for 12% Tax (Red)

The sum total of Cell AI2:AI17 goes to output cell “H36” if the value is for 18% Tax (Blue)  and goes to output cell”H37” if the value is for 12% Tax (Red)

Sir I hope you have understood my problem and you shall help me by giving me the conditional formatting formulae for the cells and as well as a VBA Code to execute the command. Thanking you in anticipation.

# Re: sum by color when colors are set by conditional formatting

hi jamil

I used your code but it does not work.  my excel is multiple criteria and ranges . is there any solution for my problem?

# Re: sum by color when colors are set by conditional formatting

Hi,

I opened your file.

the reason you get #NAME error is becuase you have not used UDF which i wrote.  you have used another UDF which works only for background color and not for conditional formatting.

to make it work, you need to set conditionally format cells and then place the UDF in the VBA module and then it will work.

I can help with troubleshooting the file, however, I am sorry I cannot build the workbook, as i do not have time.

# Re: sum by color when colors are set by conditional formatting

I cannot tell what would be the issue, unless i see a dummy example file.

# Re: sum by color when colors are set by conditional formatting

tanks your answer.. it is my file

# Re: sum by color when colors are set by conditional formatting

@Jamil Mohammad Thank you for keeping this alive for so long.  I have attached a recent project to count cells and am running into an issue with the formula not updating until it is either clicked on or it is behind cell updates by 2 inputs.  What am I doing wrong?  Changing the "daily usage" on the attached sheet should give you the same result I am seeing.

TIA

# Re: sum by color when colors are set by conditional formatting

Hi Mike,

the UDF works as it should.  your conditional formatting in the cell H2 is that if the cell value is smaller than 5 then turn it red and that is how it now count one because the color of that cell is red.  and in cell M3 color is yellow and in your range there is no cell with yellow color, so UDF returns zero.

# Re: sum by color when colors are set by conditional formatting

hi samaneeh

if you read my earlier posts, you will see that I have mentioned to other users the same thing that "(you conditional formatting applied range) and (range used in the UDF) both should be the same, otherwise it would not work. Try to do that and it will work.

# Re: sum by color when colors are set by conditional formatting

@Jamil Mohammad  thank you for your reply.  I understand but the calculation does not change as the data is updated.  It requires a "got focus"/double click to get the sheet to update after a change.  See the attached example, the field color can be updated but the count is always one update behind.  In the attached you will see the cell count for yellow is 0 but should be one.  Save the file and reopen or double click the formula field and it updates?  This always leaves my count off by 1 change.  Any advise?

# Re: sum by color when colors are set by conditional formatting

Hi, I have put some additional codes in the attached workbook that forces recalculation and that should take care of the issue you described.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies