PDA

View Full Version : highlight cells based on various criteria and the calculate the number of cells highl



sinamon
05-30-2011, 08:11 AM
Trying to highlight cells based on various criteria and the calculate the number
of cells highlighted, have no idea where to begin so here the best example I can give.

if cell G:5-G:200 equal 0
if cell I:5-I:200 contains the text #EANF#
if cell k:5-k:200 contains the text #EANF#
if cell L:5-L:200 contains the text #EANF#
then highlight only the cell ranges C:5-C:200 and D5-D200 that meet all the above criteria in black.

Then I need to a formula in cell N:2 to calcualte the total sum of Blacks highlighted.

Thanks if someone can help.

Bob Phillips
05-30-2011, 08:50 AM
Conditional formatting in C5:D200 with a formula of

=AND(G5=0,ISNUMBER(FIND("EANF",I5)),ISNUMBER(FIND("EANF",K5)),ISNUMBER(FIND("EANF",L5)))

and this formula in N2

=SUMPRODUCT(--(G5:G200=0),--(ISNUMBER(SEARCH("EANF",I5:I200))),--(ISNUMBER(SEARCH("EANF",K5:K200))),--(ISNUMBER(SEARCH("EANF",L5:L200))))

sinamon
05-30-2011, 09:11 AM
Again thanks for the quick responses, seems simple enough but how do I implement this? I created a new module and entered the first code you provided but im getting error. Is there something before and after this I should be putting?
Sorry total excel vba noob
Thanks

sinamon
05-30-2011, 01:21 PM
ok im pretty sure i figured out how to implament this but seems the formula is not doing anything, but here are the steps im taking.
I highlight the cells C5-D200 and select the conditional formating tab
I create new formating rule, I select use a formula to determine which cells to format, I enter in your formula and then I select formating color and choose the color and apply. It accepts the formula but nothing seems to change in the spreadsheet. heres a sample of the spreadsheet
6052
Thanks

Bob Phillips
05-30-2011, 11:15 PM
You have applied CF correctly, but you said I could contain #EANF#, it doesn't, it is just numeric. I also misunderstood by your saying contains #EANF#, so you can simplify the formula to just

=AND(G5=0,$K5="#EANF#",$L5="#EANF#")

sinamon
05-31-2011, 10:49 AM
So if Im correct I should just enter this formula in conditional formating rules
=AND(G5=0,$K5="#EANF#",$L5="#EANF#")
choose the format color apply and it should execute
the task, but I try this and still nothing happens.
What am I missing? Did it work for you in the
example highlight.xlsx?

Bob Phillips
05-31-2011, 01:33 PM
Yes it did, I tried it on your workbook.