Forum Discussion
Conditional Formatting possibly
Hi guys
I'm setting up my work schedule in excel so I can have an over view of my shifts I have my set up complete i.e.. A1&B1 Merge = Day, A2&B2 = Date, A3= start, B3=End, this is repeated across the rows for 7 days this only has information typed in on my scheduled shifts i also have additional rows below for extra shifts and overtime (these work perfect) however on my scheduled days on I'm trying to get the cells for the example A2/B2, A3, B3 and A4/B4 to highlight only if information is available in A3 same for across the row for each day any help would be much appreciated
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | |||||||
| 29/12/2025 | 30/12/2025 | 31/12/2025 | 01/01/2026 | 02/01/2026 | 03/01/2026 | 04/01/2026 | |||||||
| 10:00 | 22:00 | 10:00 | 22:00 | 10:00 | 22:00 | ||||||||
| 00:00 | 12:00 | 12:00 | 12:00 | 00:00 | 00:00 | ||||||||
| 09:30 | 23:57 | ||||||||||||
| 00:00 | 14:27 | 00:00 | 00:00 | 00:00 | 00:00 | 00:00 | |||||||
2 Replies
The easiest thing to do is insert a column to the left of column A. Highlight the range (should now be B2:O4) then in conditional formatting you want a new rule and based on formula. The formula will be something like:
=(B$3<>"")*ISEVEN(COLUMN(B$3))+(A$2<>"")*ISEVEN(COLUMN(A$2))
which basically is always checking row 3 and either the cell in the same column (e.g. B3) if that column is even or the cell is the column before (e.g. A3) if this column is odd. as you can see adding that extra column to the left and pushing everything over 1 column makes it easier to check both this column and the column before (which wouldn't be possible if the range started in column A)
Could you post a screenshot showing the expected result?
