Conditional Formatting

liampog

Active Member
Joined
Aug 3, 2010
Messages
308
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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
Back
Top