Format cells based on row and column references

[Minx_Minxy]

Hello,

I have a file with 2 sheets Named:

Holiday Calendar

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1						2022
2						BH	Bank Holiday UK&I			BH	Bank Holiday UK			BH	Bank Holiday Ireland					Holiday
3
4						January
5		Carried Fwd	In Lieu	Entitled	Left Over	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
6	Name					01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
7	Joe							BH																									1			1
8	Smith							BH	1	1	1	0.5
9	Sam							BH
10	John							BH
11	Karen							BH														1	1	1
12	Harry							BH																								1	0.5
13	Kevin							BH
14
15
16						February
17		Carried Fwd	In Lieu	Entitled	Left Over	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon
18	Name					01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28
19	Joe
20	Smith
21	Sam											1	1	1	1	1
22	John
23	Karen
24	Harry																			0.5	1
25	Kevin
Holiday Calendar

And Daily Planner

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Monday		Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday
2	Joe	03 - Jan		04 - Jan		05 - Jan		06 - Jan		07 - Jan		08 - Jan		09 - Jan
3	Smith										Half Day
4	Sam
5	John
6	Karen
7	Harry
8	Kevin
9	Joe	10 - Jan		11 - Jan		12 - Jan		13 - Jan		14 - Jan		15 - Jan		16 - Jan
10	Smith
11	Sam
12	John
13	Karen
14	Harry
15	Kevin
Daily Planner

What I need is when 1 is entered in Holiday Calendar, then the corresponding cell in Daily planner is filled with blue. If 0.5 is entered then it turns blue and mentions Half Day. For example, Smith is on holiday from the 04th to 06th and half a day on 07th of January in the Holiday calendar (cells I8:L8), so in the Daily Planner for the corresponding days the cells are formatted blue from 04th to 06th of January and on the 07th is blue + Half Day on it (cells E3,G3,I3,K3)

I would like it applied for all 12 months of the year, both sheets are setup like the ranges provided up till the end of 2022.

I don't believe conditional formatting supports this. If it does how do I do it?

I have very little knowledge in macro but I am willing to use it if would suit best for this situation.

 

[6StringJazzer]

It looks to me like your big problem is that the first sheet is organized by month and the second sheet is organized by week. If they were organized the same way you could definitely use conditional formatting do this. But the way you have it makes it very difficult to start with a person/date on the second sheet and find the corresponding cell on the first sheet.

Another problem is using merged cells in the second sheet. Merged cells nearly always cause problems and in this case they will.

One thing that is fortunate is that the dates are actual Excel dates so they can be compared. Another suggestion is to also make the days in Holiday Calendar refer to the actual dates and format them as "Ddd" so you don't have to manually change them for every month.

This user-defined function (UDF) should work but I had to re-create your file from scratch to test it. Then you would use this in a conditional formatting rule.

See linked file for example using your mock-up.

Dropbox

www.dropbox.com

That is a dropbox link and is not guaranteed to be here forever.

Option Explicit

Public Function IsOnHoliday(Cell As Range) As Variant
  
   Dim R As Long, C As Long
   Dim HolidayDate As Range
   Dim DateCell As Range
   Dim LastRow As Long
  
   If Cell.Count > 1 Then
      IsOnHoliday = "#RANGE!"
   Else

      ' Find the date
      Set DateCell = Cell.Offset(0, -1)
      Do Until DateCell > 0
         Set DateCell = DateCell.Offset(-1, 0)
      Loop
     
      ' Now find the corresponding date in Holiday Calendar
      R = 1

      With Worksheets("Holiday Calendar")
     
         ' Find the date
        
         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         Do
            ' Find the date row of the next month
            Do
               R = R + 1
            Loop Until IsDate(.Cells(R, "F")) Or R > LastRow
           
            If R > LastRow Then Exit Do
           
            ' See if the desired date has a match in this month
            Set HolidayDate = .Rows(R).Find(what:=CDate(DateCell), LookIn:=xlFormulas, lookat:=xlWhole)
        
         Loop Until Not HolidayDate Is Nothing
        
         If R > LastRow Then
            IsOnHoliday = "#DATE!"
            Exit Function
         End If
        
         ' Find the person
         Do Until .Cells(R, "A") = Cell.Parent.Cells(Cell.Row, "A")
            R = R + 1
         Loop
        
         IsOnHoliday = IsNumeric(.Cells(R, HolidayDate.Column).Value) And .Cells(R, HolidayDate.Column).Value > 0
        
      End With
   End If

End Function

Private Sub test()

   Debug.Print IsOnHoliday(Sheet16.Range("E3"))  

End Sub

 

[Minx_Minxy]

Hi StringJazzer, thank you for the feedback regarding the merging cells, I'll keep it mind next time!

Apologies in advance, my knowledge in macro is very limited, do I have to change anything when I copy the code to the sheet I need? If so what is it?

Will it automatically work as information is entered? Or do I have to assign to an object like button?

 

[Minx_Minxy]

Hello StringJazzer,

I went back to your answer! My apologies for overseeing that the code has been applied on the conditional formatting. However, on the spreadsheet you've provided I tried to test it by adding 1s to other cells in the Holiday Calendar but the cells in the Planner aren't turning blue.

What should I be doing to make it work?

 

[6StringJazzer]

The code must be placed in a standard module. If you do not have any modules, create one and its name will default to Module1. The code will not work if you put it in a worksheet module.

All of this is demonstrated in the file I linked.