sum by color when colors are set by conditional formatting

Pankaj_HSR 

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.

Jamil 

Hello Mr. Mohammad,

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.

Hi Jo_Nolaj,

I looked at your workbook, the conditional formatting range and the range used in the UDF was not consistent. i changed it now and it works. plz see attached.  please note that instead of 4 CF you could set them with OR function all in one. plz see the CF which i set. the only thing is that CF is replicated for each row using format painter. 

Hi, Sir Jamil! Could you kindly help me how to make the UDF and formula you shared on counting the number of rows colored thru conditional formatting work? I copied and paste the UDF for this and followed the formula in your attached sample file, but I cannot get it right. The total is always zero. Here's the file I am working. Thank you very much!

Hi waran,

I checked your file. you have sent Conditional formatting for the same color (i.e red) on the same range. if you have multiple condition for the single color. then it needs to be put in one single formula, with OR function. the UDF does not work on multiple condition set for the same color with two different CF rule.

Hi Jamil, 

I would like to count the number of coloured cells in a set of data regardless of the numbers within the coloured cells. These colours are determined by conditional formatting. The colours include green, amber and red.

I have applied your VBA code to count colours, however it does not count correctly. It only counts the amber coloured cells from the data set.

From the dummy data I have attached, the number of reds should be 2, the number of ambers should be 1 and the number of greens should be 2. 

Could you please have a look at the dummy data attached to determine why there is an issue and best appropriate actions.

Thanks in advance,

Waran. 

Jamil,

Thanks for replaying.

It's true... on a previous post I said that the code you gave me works and I stand by that.

The request after that was of a different scenario... the formulas need to be applied in a totally different way...

Also, I already deleted all the CF... the file I send earlier belongs to a bigger workbook where all that conditional formatting is used and some how it stayed (I forgot to check and clean) in this file and the CF even linked to a Sheet4... The CF in the file now is only what's needed.

But no worries... I already figure it out...

Please see attached WORKING file... I upload it here for reference in case any one else might need the same solution.

If you inspect the file, you will see that is not the same problem although is has to do with counting with conditional formatting... which is what this thread is about.

Thanks again Jamil.

Jamil, thank you for helping us...

I have six clusters of data, each is 11 columns by 70 rows.

There is Conditional Formatting in each cluster to the effect that if on another cell (say N2) I type a value and that value is found within all the six clusters of data then all the cells containing that manually inputted value will be highlighted (to a specific color that I already set).

Up to that point all is fine.

Now, I need to display MATCH FOUND when all cells in a row (in one cluster of data) have been conditionally formatted and change the font color to all the cells that meet the criteria.

I know it sounds confusing... I'm dizzy myself... but to illustrate the scenario, I attached a file with dummy data.

In the attached file, to illustrate my point, I manually changed the FONT COLOR to all the cells that meet the criteria (all the cells in that row have been conditionally formatted with different colors, obviously), and I also manually added at the bottom of each cluster of data a MATCH FOUND cell since that's what I also need to see as I manually inputted specific numbers (in another area of the sheet, N2 through N7) that I'm looking for.

I tried with the following formulas but to no avail (the VB module is also in place)...

=REPT("MATCH",COUNT(1/MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,N(P$2:Z$71=O2)))=11) array formula

=IF(SUM(--(MMULT(TRANSPOSE(ROW(P$2:Z$71))^0,--(O2=P$2:Z$71))>0))=11,"MATCH","") regular formula

In the examples I have found in this forum, they count how many cells have been conditionally formatted. My scenario is slightly different:  I need to be able to SEE when all the cells in row of a cluster of data have been conditional formatted and to display "MATCH FOUND" and change the font color of those cells.

Thank you for helping.

This is awesome, thank you so much for your help!

Dear David,

The issue with your file was, that UDF was using single row range as the range input, while the Conditional Formatting was applies from L2 to Z8 .   So, i modified the file Conditional Formatting and now it works. 

Also inside the CF formula, you used two functions, while this was not cause of error, but to make it simple and faster, i simplified it with a single function of "Columns".

 so =COLUMN(L2)-COLUMN($L2)+1>=$AE2  became =COLUMNS($L2:L2)>=$AE2

Please download and see attached workbook.  

Dear Jamil,

Your help has been really enlightening. If you are available, could you please help me by taking a look at his file?

I am using the VB code you created, but the formula doesn't seem to recognize the CF color and I keep getting the #VALUE! refference.

Could you let me know how to fix this?

Thank you Jamil- so easy when you know how.  Much appreciated.

Hi Noel,

The VALUE error because your conditional formatting was not set using formula.

If you read my earlier post, I have highlighted that UDF only works if the conditional formatting is set by using formula and not the built-in features of CF.

So, in the attached workbook, I have set the Conditional Formatting using formula and then the UDF works.

please note that SUBTOTALs are not counted as duplicate.   please see attached file and examine it by yourself.

Hope it helps.

Jamil I have the same issue as Hyosun.  I've entered the VBA code but cannot get it to work.  No matter what I do it keeps showing the #Value.  I'm trying to count duplicate text based values which seems to be causing some problems. Can you point me towards where the error might be occurring and or a resolution.

Thanks

Noel

Jamil,

I have made this adjustment after adding more sheets and all is working just as it should.  Thank you so much for all your help on this issue!

Jamil,

Please disregard my previous message.  After I enabled Macros, saved, and closed the document, I opened it back up and it was running the formulas properly.  However, I did have to double click the cell and press Enter to refresh the formula so that it would calculate the correct amount.  Regardless, the numbers work and they do not change when I do this to other output cells on separate sheets.  I will need to add what you did on these 4 sheets to several other sheets to get them to work properly as well.  Could you let me know what you changed so that I can make the same edit to the full workbook and its additional sheets?

Thank you!

Hi Jamil,

I am running into an invalid name error with each COUNTConditionColorCells output cell stating that the formulas contain unrecognized text.  I did enable macros on the sheet after downloading it.  However, I can see from the document preview that the first 2 sheets are calculating correctly for both green and red text cells, while the 3rd and 4th sheets are calculating green text cells properly but not the red text cells (sheet 3 shows 6 while it should be 9, sheet 4 shows 10 while it should be 7).

Thank you.

Hi Steve,

thanks for uploading the sample data.

Can you please test the attached version?

Hi Jamil,

When I refresh using F9, the current sheet will show the correct data in the output cell, however this appears to interfere with the output cells on other sheets in the workbook which are trying to sum the color condition using a range of cells on their respective worksheet.  I have attached a sample of the workbook I'm working on to hopefully better illustrate the situation.  Perhaps this is an issue with my conditional formatting formulas, however each sheet is calculating the correct number of colored cells on its own, leading me to believe that this is not the case.  Could you take a look and suggest what I might do to fix this?  If it requires a UDF for each sheet individually, could you help me with a rewrite to make the code sheet specific?

Thank you very much for your time!