Excel VBA Color cell under month and year depends on cell value (date) in other sheet-CodePudding

I have Two Sheets:

EXCEL

EXCEL2

RESULT

Basically What ever date is in image (EXCEL 2) in front of Names same date will represent in form of color in front of name under same month and year in excel sheet two that is (image EXCEL).

Sorry I can not able to figure out any code or anything totally blank. Hope get some help here Thanks RESULT SOMTHING LIKE IN IMAGE 3 if date is changed in Sheet color and month should update as new date

CodePudding user response：

If I understand you correctly.....

Before running the macro, the condition of the two sheets (sh1 and sh2) is something like this :

The expected result after running the sub is like image below

Sub test()
Dim dt As Date: Dim t0 As Date: Dim t1 As Date: Dim t2 As Date
Dim rgT As Range: Dim rgN As Range: Dim c As Range: Dim cell as Range

With Sheets("sh2")
    Set rgT = .Range("A2", .Range("A2").End(xlDown))
End With

With Sheets("sh1")
    .Range("A2:Z1000").Interior.Color = xlNone
    Set rgN = .Columns(1).SpecialCells(xlConstants)
End With

dt = "1-jan-2023"

For Each cell In rgT

    t0 = cell.Offset(0, 1).Value
    t1 = cell.Offset(0, 2).Value
    t2 = cell.Offset(0, 3).Value

    Set c = rgN.Find(cell.Value, lookat:=xlWhole)
    
    If Not c Is Nothing Then
        c.Offset(0, DateDiff("m", dt, t0)   1).Interior.Color = vbGreen
        c.Offset(0, DateDiff("m", dt, t1)   1).Interior.Color = vbRed
        c.Offset(0, DateDiff("m", dt, t2)   1).Interior.Color = vbBlack
    End If
    
Next

End Sub

rgT variable is the range of column A in sh2. It assume there'll be no blank cell in the whole table (cell A2:D6).

rgN variable is the range of column A in sh1.

Basically, the code just get the month difference from the starting "benchmark" date with each date in the test/test1/test2 in sh2. In this case example, the "benchmark" is the dt variable which value is 01-January-2023.

It loop to all the cell within the rgT, and check if the looped cell value is found in rgN as c variable then it fill the needed cell with the needed color.

FYI, if the sh1 looks like in your image, then you need to add 2 for each c.offset because there is one column in between the cell with the name and the starting "benchmark" cell.