Change color of cell depending on character in string

**delirium** · 01-07-2011, 03:30 PM

I have a string of characters in Column D which will look like so:

==MTWRF
==MTWR=
Y=MT=RF
==MTW=F
Y=MT=RF

The = represents a day off.
Columns F thru L are labelled With days of the week (starting w/ Sat)

F=Sat
G=Sun
H=Mon
I=Tues
J=Wed
K=Thurs
L=Fri

I would like to shade the corresponding cell black where the = sign represents a day.

==MTWRF would shade F (Sat) and G (Sun)
==MTWR= would shade F (Sat), G (Sun) and L (Fri)

It seems I should be able to use conditional formatting, but I am not certain what formula to use.

Thanks for any help you can provide (I can attach the spreadsheet if that would make it simpler)

**NBVC** · 01-07-2011, 03:36 PM

Assuming you are starting in F2, select F2:M(last row) and go to Format|Conditional Formatting... choose Formula Is and enter formula:

=IF(MID($D2,COLUMNS($F$1:F$1),1)="=",1,2)

then click Format and choose from Pattern Tab.

**delirium** · 01-07-2011, 04:41 PM

Originally Posted by NBVC

Assuming you are starting in F2, select F2:M(last row) and go to Format|Conditional Formatting... choose Formula Is and enter formula:

=IF(MID($D2,COLUMNS($F$1:F$1),1)="=",1,2)

then click Format and choose from Pattern Tab.

I simplified it somewhat ... =MID(D4,1,1)="=" and it seems to work.

thanks so much.

**NBVC** · 01-07-2011, 04:47 PM

You are correct, it should have been simplified...

I had done a test in a cell with a regular formula and copied that instead of the one I had in the conditional formula:

=MID($D2,COLUMNS($F$1:F$1),1)="="

Note, though that the COLUMNS($F$1:F$1) is what allows for the formula to "adjust" to next character in the original string in column D. Also you need the $ before the D to keep an absolute reference to column D