conditional formatting

[biglb79]

is there a rule I can setup so that the account number in column B highlights in red if I have a word in the description column I that would make it jump out as the incorrect account?

like bonus payments go to 6950 and commission payments to 6955. I sometimes accidentally post them to the wrong account, but if I had something that says if 6950 in column B and commissions column I it's wrong

 

[Fluff]

Do you want to highlight col B if col I has anything, or only if col I has a specific word?

 

[Joe4]

Yes, you would have a rule something like:

=OR(AND($B1="6950",ISNUMBER(FIND("commission",$I1))),(AND($B1="6900",ISNUMBER(FIND("bonus",$I1)))))

Note that the formula is written as if the value in B1 is a Text entry. If it is numeric, remove the double-quotes from around those numbers.

 

[biglb79]

thanks so much for your help! i have the 6955 working if it says bonus in column I, but I'm struggling having the 6950 one work if it says commission in column I

 

[Joe4]

Note that FIND is case sensitive. So it would find "commission", but not "Commission".
If we use Search, then it does not care about case, i.e.

=OR(AND($B1="6950",ISNUMBER([COLOR=#0000ff]SEARCH[/COLOR]("commission",$I1))),(AND($B1="6900",ISNUMBER([COLOR=#0000ff]SEARCH[/COLOR]("bonus",$I1)))))

 

[biglb79]

thank you so much for your help!!!

 

[Joe4]

You are welcome.