Hopefully simple conditional format

ratt2581

Board Regular
Joined
Nov 11, 2006
Messages
100
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

Board Regular
Joined
Nov 11, 2006
Messages
100
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
It worked for me, but I had to turn on the R1C1 reference style first, in the Tools, Options, General menu.
 

Forum statistics

Threads
1,181,659
Messages
5,931,273
Members
436,786
Latest member
Deniel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top