Hello!
I'm trying to figure out how to loop through multiple columns headers, but in each column, I need to loop through each character in the column and recognize a specific number. If there is a match, I'd want it to highlight that column header. For example:
In the attached file starting in the 'Start Page' tab, I'd like to loop through the highlighted range (this will need to be dynamic as this range varies) and loop through the range Y1:AO1 on the 'Results' tab. The range Y1:AO1 will also need to be dynamic but will always start on Y1.
This is where to me it gets a bit more complicated: As mentioned above i want it to loop through range Y1:AO1, however first I want it to loop through each character in each cell in the range Y1:AO1. If it finds a number that matches the number in the range in the highlighted 'Start Page' tab, then I'd like it to highlight the header column in the 'Results' tab as shown.
The code i have written so far works, but only if the numbers in the header columns in the 'Results' tab are single digits. It does not work if the columns reads 'CUMULATIVE CONTRIBUTION BY SOURCE 12' for example. I understand why because the IsNumeric function is only pulling one digit at a time, however im not sure what other route to go with this. Here is my code so far:
Sub Test2()
Dim SrcLR As Long
Dim myvalue As String
Dim i As Long
Dim a As Long
Dim number As Integer
Dim ColEnd As Integer
Dim col As Integer
Dim intstart As Integer
SrcLR = Worksheets("Start Page").Range("D" & Rows.Count).End(xlUp).Row
ColEnd = Worksheets("Results").Cells(1, Columns.Count).End(xlToLeft).Column
Worksheets("Start Page").Activate
For a = 2 To SrcLR
For col = 25 To ColEnd
myvalue = Worksheets("Results").Cells(1, col).Value
intstart = InStr(myvalue, "-") 'used this variable to loop only up to the "-" in myvalue
For i = 1 To intstart
If IsNumeric(VBA.Mid(myvalue, i, 1)) Then
number = CInt(VBA.Mid(myvalue, i, 1)) 'used this variable to make the string value into a number; otherewise loop wouldnt recongnize match with Range("D" & a).Value
If Range("D" & a).Value = number Then
Worksheets("Results").Cells(1, col).Interior.Color = vbYellow
End If
End If
Next i
Next col
Next a
End Sub
So my question is, how do i loop through the range on 'Start Page' and Loop through each character on the 'Results' tab range, and if the numbers match, highlight the header column on the 'Results' tab? I've attached the file I'm working on which hopefully illustrates this a bit better. Any help is greatly appreciated!