HighLight cells range

[Vincent88]

Hi All,
I need help in the following code.
In Worksheet "Data", each cell value in Column AC represents the column number and that of Column AD represents the row number in another worksheet.
I need to loop thru column AC if the difference of the two values is over 5 and the value of the two cells in next column AD is the same, then trigger to format the cells in another worksheet.

Sub highlightCells35()
Dim ws As Worksheet
Dim wsm As Worksheet
Set ws = Worksheets("Data")
Set wsm = ActiveSheet

Dim lastrow As Long
 lastrow = ws.Range("AA1").End(xlDown).Row
 Debug.Print lastrow
Dim iCol As Range
Dim rngData As Range
    Set rngData = ws.Range("AA1:AA" & lastrow)
Application.ScreenUpdating = False
Application.EnableEvents = False

With rngData
    For Each iCol In rngData.Columns(3)  'in Column AC
        If (iCol.Offset(1).Value - iCol.Value) - 1 > 5 And iCol.Offset(, 1).Value = iCol.Offset(1, 1).Value Then
                
                  Dim areaH As Range
                  areaH = wsm.Range(Cells(iCol.Offset(, 1), iCol), Cells(iCol.Offset(1, 1), iCol.Offset(1)))
                  With areaH
                      .Font.Underline = xlUnderlineStyleSingle
                  End With
              Else
                  With areaH
                     .Font.Underline = xlNone
                  End With
        End If
    
    Next iCol
End With

Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub

AgentProposal_Roster0728_1025M.xlsm
	AA	AB	AC	AD
1	D3	D	4	3
2	F3	F	6	3
3	I3	I	9	3
4	J3	J	10	3
5	W3	W	23	3
6	X3	X	24	3
7	AD3	AD	30	3
8	AE3	AE	31	3
9	AK3	AK	37	3
10	AL3	AL	38	3
11	H4	H	8	4
12	I4	I	9	4
13	J4	J	10	4
14	M4	M	13	4
15	W4	W	23	4
16	X4	X	24	4
17	AD4	AD	30	4
18	AE4	AE	31	4
19	AK4	AK	37	4
20	AL4	AL	38	4
21	J5	J	10	5
22	L5	L	12	5
23	W5	W	23	5
24	X5	X	24	5
25	AD5	AD	30	5
26	AE5	AE	31	5
27	AK5	AK	37	5
28	AL5	AL	38	5
29	C6	C	3	6
30	H6	H	8	6
31	I6	I	9	6
32	J6	J	10	6
33	Q6	Q	17	6
34	W6	W	23	6
35	X6	X	24	6
36	AD6	AD	30	6
37	AE6	AE	31	6
38	AK6	AK	37	6
39	AL6	AL	38	6
Data

 

[Fluff]

Try

    For Each iCol In rngData.Columns(3).Cells  'in Column AC

 

[Vincent88]

Hi Fluff,
It works. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.