Hopefully simple conditional format

[ratt2581]

Does anyone know what the formula would be to apply a conditional format to Cell5 if cell 1 ended in (XY) and Cell5 did not contain a certain string

For example if(right(R1C1,2)="(XY)" and R1C5<>"Good" then Highlight R1C5 Green.

I would like this to to apply to all rows in one sheet

Thanks

 

[brian.wethington]

Formulais

=And(Right(r1c1,4)="(XY)", r1c5 <> "Good")

I am not sure if you want "XY" or "(XY)" but I think you get the picture. Start at the top left of the data you want to apply this to and select down and right and then apply the CF.

 

[ratt2581]

thanks for the help (and sorry for the delay) but I'm currently getting an error when I try to apply that conditional format. Is one able to use R1C1 relative cell referencing in the conditional formats? I tried exactly as you had it and it gave me an error on the formula?

 

[Gerald Higgins]

It worked for me, but I had to turn on the R1C1 reference style first, in the Tools, Options, General menu.

 

[ratt2581]

that would do it

thanks for the help