CONDITIONAL FORMATTING IN EXCEL - off of a range of cellsSolved/Closed

Question

Hi
I've never used these forums before but this issue has been bugging me for some time now.... so i thought i would leave it to the experts....

I have one cell that i want to apply formatting on based off of the condition that another cell has some text in it.

Eg: Shade A1 BLUE IF B1 has the text "TRUE" in it

So in Conditional formatting I just have =B1 = "TRUE"

This works fine.

What if I want A1 to turn Blue if other cells are TRUE in column B? So it could be a whole range....

e.g

B1 = FALSE
B2 = FALSE
B3 = TRUE
B4 = FALSE etc.....

Within  cell A1 I opened conditional formatting and I tried 

=(B1:B200) = "TRUE"

But this did not work?

I also tried =RANGE(B1:B200) = "TRUE"

But this did not work either?

Any ideas?

TrowaD · Answer

Hi Mounta1n,

Try this:
=IF(COUNTA(B1:B200="TRUE")>0,TRUE,)

Best regards,
Trowa

Mounta1n · Answer

Hi Trowa (anyone)

I have tested your solution and this is not working? Once I insert the condition the target cell A1 remains TRUE or shaded Blue for any value chosen in column B?

Please note that I have a drop down box in Column B with a number of outcomes but that shouldn't affect the formula? I even changed all the outcomes in the list so that none contain the word TRUE and still A1 remained Blue?

The logic seems sound but not sure why its not working?

Any other ideas?

Mounta1n · Answer

Problem solved....its a COUNTIF function not a COUNTA!

=IF(COUNTIF($Q$4:$Q$182,"No - differences unexplained above materiality")>0,TRUE,)

But like the logic!

Thanks

sivri526 · Answer

You select A1:A200 then new rule from validation data
=B1:B200=TRUE 
Not: you dontrite appostrove "TRUE"
i exampled its solved

CONDITIONAL FORMATTING IN EXCEL - off of a range of cells

4 responses

Similar discussions

Subscribe To Our Newsletter!