Conditional Formatting

liampog

Active Member
Joined
Aug 3, 2010
Messages
290
Hi

I have the following data on a sheet. It is basically a calendar that shows a persons days off.

Role 0 are fixed days off people. Role 1 is to be ignored.

I want a conditional formatting to be true when the following are satisfied:

1. Column B = 0
2. If the day (Row 1) for the corresponding date is TRUE in Columns C to I.

I can't figure out what formula I need to put into the conditional formatting.

The columns extend beyond Column W up to the last day of the year (so 365/366 columns in total).

I've placed X in the cells that I want to colour (there won't actually be any data in the cells and the X's are just to clarify the cells which will be true in the conditional format).

I hope I've worded this clearly.

Thanks
Liam

ABCDEFGHIJKLMNOPQRSTUVW
1NameRoleMoTuWeThFrSaSuMoTuWeThFrSaSuMoTuWeThFrSaSu
21/12/13/14/15/16/17/18/19/110/111/112/113/114/1
3Joe Bloggs0TRUETRUETRUExxxxxx
4John Smith1
5Alan Jones0TRUETRUETRUExxxxxx
6John Doe0TRUETRUETRUExxxxxx
7Mary King1
8James May0TRUETRUETRUETRUExxxxxxxx

<tbody>
</tbody>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,835
Office Version
2007
Platform
Windows
The formula is based on the date you have in cell J2, since the day in J1 is incorrect, it should be Tuesday.

Change 8 for the last line with data.

=IF($B3=0,1)*INDEX($A$1:$I$8,MATCH($A3,$A$1:$A$8,0),MATCH(LEFT(TEXT(J$2,"ddd"),2),$A$1:$I$1,0))

Change W for the last column with data.
Applies to:
$J$2:$W$8
 

liampog

Active Member
Joined
Aug 3, 2010
Messages
290
Hi Dante

Thanks, that's brilliant, and actually the formula makes perfect sense to me :) just couldn't work it out for myself.

To be honest, I just made the days and dates up so was able to modify the formula to work correctly.

Thanks again
Liam
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,835
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,134
Messages
5,338,430
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top