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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.