Conditional Formatting-Please help

[Radsan]

In a decision matrix, I want to change the colour of the cells in column range I3:I216 based on a comparison between two strings in D3:D216 and L3:L216. The values in column D and column L can only be either "B MAJOR" or "B MINOR". The comparison is done between two cells containing Text across the same row.

For example I want I3 to be green if both D3 and L3 contain the same string or red if they both contain different strings. These string values are coming from If statements and VLookups.

I tried a helper column Y where I used if(EXACT(D3,L3)=TRUE,1,0) to populate the column with 1's and 0's. But I cannot get I3 to change colour depending on Y3 values of 1 or a 0 by conditional formatting.

Formula tried was
Condtional 1: formula is Y3=1..Green pattern
Conditional 2: formula is Y3=0...Red Pattern

I tried a lot of other combinations, but cannot get the column formatted and change colour depending on the conditions, I want.

It probably needs a programmatic solution, but I am have no idea how to go about writing VB code. I am at my wits end and would be grateful for any help.

 

[barry houdini]

You shouldn't need a helper column. Just use conditional formatting "formula is" in I3

=(D3=L3)*(D3<>"")
green

=D3<>L3
red

 

[Radsan]

You shouldn't need a helper column. Just use conditional formatting "formula is" in I3

=(D3=L3)*(D3<>"")
green

=D3<>L3
red

Thank you for your reply. But that does not seem to work though no errors are displayed. The contents of the cells in column I and column L is coming from a vlookup. Does that make a difference?

I tried this and it works:

=OR(AND(D3="B MAJOR",L3="B MAJOR"),AND(D3="B MINOR",L3="B MINOR"))...Green

=OR(AND(D3="B MAJOR",L3="B MINOR"),AND(D3="B MINOR",L3="B MAJOR"))...Red

A very rudimentary solution which only works because there are only two values that can go in the comparison cells.

I am still intrigued by your solution, especially the use of the asterix/multiplier. Can you please let me know what the logic is behind that? I am very interested..

 

[daniels012]

For example I want I3 to be green if both D3 and L3 contain the same string or red if they both contain different strings. These string values are coming from If statements and VLookups.

For the Green condition just put
=D3=L3
For the Red try
=D3<>L3
EDIT:
I did mean to tell, this is for Conditional formatting!! Also, make sure the option for "Formula Is" is selected, not "cell Value Is"!!

Michael

 

[barry houdini]

I'm not sure why my suggestion doesn't work. Sometimes you need to be careful with conditional formatting that Excel doesn't add quotes to the formulas.

In this

=(D3=L3)*(D3<>"")

the * just acts like AND, you could easily use

=AND(D3=L3,D3<>"")

the second test, D3<>"" is simply added so that the formatting is not applied when D3 and L3 are equal....but they are both blank

 

[Radsan]

For the Green condition just put
=D3=L3
For the Red try
=D3<>L3
EDIT:
I did mean to tell, this is for Conditional formatting!! Also, make sure the option for "Formula Is" is selected, not "cell Value Is"!!

Michael

Thanks for your interest. But that does not work. It was one of the first things I tried.

I'm not sure why my suggestion doesn't work. Sometimes you need to be careful with conditional formatting that Excel doesn't add quotes to the formulas.

In this

=(D3=L3)*(D3<>"")

the * just acts like AND, you could easily use

=AND(D3=L3,D3<>"")

the second test, D3<>"" is simply added so that the formatting is not applied when D3 and L3 are equal....but they are both blank

Thanks for explaining the logic. I did notice the extra quotes that excel was putting in. See following images:

First, the formatting that works followed by the formula, where the sample row is row 17 where the values are different, so I am formatting I17, with a comparison between D17 and L17.

Second, I changed the formula in I17 to the one suggested earlier, but unfortunately does not work.

I put the formula in and reopened the conditional formatting for I17 to make sure extra quotes were not included.

Thanks for your help. Maybe it does not work because they are strings? Don't really know.

 

[daniels012]

Actually, if BMINOR AND BMAJOR are the only text that can be entered in those cells, your original solution will work as long as the conditions are switched.

Put the red condition first,
then the green second.

For the Red try

=D3<>L3

For the Green condition just put

=D3=L3

Michael

 

[Radsan]

I realize what I have been doing wrong.

All your solutions work. Sorry and please accept my apologies and thanks for your interest and time.