Formula/Conditional Formatting - HELP!!

[netchie]

Hi,

I need to track all colors that I have on A1:A5 and make sure that all of them have been listed on column B. It's a checklist and making sure that all of them have been equal from column A.

For example, let's say my Column A below is from Red to Purple, if I put "Blue" on B1, The cell that has the same color in Column A will be triggered and will be highlighted (A2). How can I get this? Also, the colors in column A are not always the same and sometimes Red is not included but Orange is. Is there a way that even if I change the color in Column A and will have the same color in Column B it will still be highlighted?

A B
1 - Red 1 - Blue
2 - Blue 2 - purple
3 - Yellow
4 - Green
5 - Purple

Please help!

Thanks in advance,
Netchie

 

[VoG]

Are these cells colored manually?

 

[Tigs81]

Hi
The Conditional Format would look something like this i think.

=IF(COUNTIF($B:$B,A1)>0,TRUE,FALSE)

set the formatting that you want.

"Is there a way that even if I change the color in Column A and will have the same color in Column B it will still be highlighted?"

I am not aware of a way of remembering unless you use macro's, to remember previous values in an array.

Thanks
Tigs

 

[netchie]

Hi VoG,

I'm so excited somebody replied to my topic, thanks

Basically, I am thinking of using the Conditional Formatting for Column A when the same color entered in Column B. I just don't know what to do like for the whole Column B1-B5, anywhere the red will be entered, the red in Column A (red can be entered in any of Column A1-5) will be highlighted.

The purpose of this spreadsheet is to make sure that all colors in Color A will be entered in Column B and if it's highlighted in Column A, it means it's already been saved in Column B. I hope I make sense Ugh!

 

[netchie]

@ Tigs81 - you rock!! It works!

Two thumbs up!

VoG - thanks on taking time checking this for me too


Thanks ,
Netchie

 

[netchie]

@ Tigs81 - by the way, what if my Column A has 30 rows instead of 5? Can these still be highlighted? I tried to add more and put the same color in Column B and it does not highlight Column a anymore with same color.

 

[Tigs81]

Hi
Just highlight the number of rows that you want and re apply the conditional format starting from row 1

Thanks
Tigs

 

[netchie]

I'm sorry for being stupid but I did highlight Column A1:A30 and also try all Column A and it didn't highlight anything anymore.

FIRST ATTEMPT:
=IF(COUNTIF($B:$B,$A$1:$A$30)>0,TRUE,FALSE)

SECOND ATTEMPT:
=IF(COUNTIF($B:$B,$A:$A)>0,TRUE,FALSE)

Both nothing

 

[netchie]

So I only need Column A1-A30 and Column B-B30 and not the whole B column. Ugh! I wanna pull my hair. I suck

 

[Marcelo Branco]

Hi,


Maybe this

Select A1:A30
CF > New Rule > Use a formula...
=COUNTIF(B1:B30,A1)

M.