Referencing different cells in order to condionally format another

**Valiantlyon** · 10-08-2009, 01:38 AM

Hi,

I'm hainvg trouble creating a formula to highlight a particular column based on a day reference in another cell.

ie. if cell L1=Monday, highlight column E
ie. if cell L1=Tuesday, highlight column F

and so forth

I'm trying to add a condional format formula to all the cells in a column. Columns E through I would all reference cell L1.

Any help would be appreciated.

**ConneXionLost** · 10-08-2009, 02:13 AM

Hi Valiantlyon,

Does cell L1 contain an actual date, or just the word "Monday", "Tuesday",...?

Cheers,

**Valiantlyon** · 10-08-2009, 02:53 AM

Here's where I sit right now:

CellL1 =TODAY()

CellM1=TEXT(L1, "dddd")

L1 creates the date, and cell M1 converts it to Monday, Tuesday, ect.

I tried referencing both cells...maybe the fact that I'm referencing a formula is my problem?

**ConneXionLost** · 10-08-2009, 03:03 AM

Just leave cell L1 as is.

For column E, use this formula in the conditional format:


=WEEKDAY($L$1,2)=1

For column F, use this formula in the conditional format:


=WEEKDAY($L$1,2)=2

For column G, use this formula in the conditional format:


=WEEKDAY($L$1,2)=3

For column H, use this formula in the conditional format:


=WEEKDAY($L$1,2)=4

For column I, use this formula in the conditional format:


=WEEKDAY($L$1,2)=5

Cheers,

**DonkeyOte** · 10-08-2009, 03:07 AM

If you wanted to have one rule for all columns simultaneously you could adapt such that the rule becomes:

=WEEKDAY($L$1,2)=COLUMNS($E1:E1)

Highlight E1:I1 (selecting E1 first) and apply the above setting format as appropriate.