Conditional Formatting

liampog

Active Member
Joined
Aug 3, 2010
Messages
305
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>
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. 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
305
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
12,618
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,270
Messages
5,623,737
Members
415,986
Latest member
C_Braga

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