HELP! VBA Highlight Cells which match value

[jakel27]

Hi everyone,

I am very new to VBA and am trying to copy cells from one sheet to another if the cells values match. I'm doing this by pasting cells which are highlighted.

Right now I just need help with highlighting cells which match.

Scenario... If Col(E) is todays date, I want to grab all the numbers from Col(C) in the same row and search Col(C) for all other rows with the same matches and highlight them red as well.

I think my code needs another range to search within, aside from the range I'm already in.

Thanks in Advance!

Sub Highlight_Cells_and_Copy()

'Highlight
Dim R As Range, WOCell As Range, datetoday As Range
Dim WO As String, Var As String
Set R = Range("C15000", Range("C15000").End(xlDown)) 'look at this range
Set datetoday = Range("E15000", Range("E15000").End(xlDown))

Columns(3).Interior.Color = xlNone

For Each Cell In datetoday

If Cell.Value = Date Then
Cell.Offset(0, -2).Interior.Color = RGB(255, 0, 0)
Var = Cell.Offset(0, -2).Value

If InStr(WOCell.Value, Var) > 0 Then <--------------------------- FIX THIS PLEASE (Search from range R and not range datetoday
WOCell.Interior.Color = RGB(255, 0, 0)
End If

End If

Next
--------------------------------------------------------------------------- Attempt 2:
For Each WOCell In R 'look at range to only today
WO = WOCell.Value
If InStr(WOCell.Value, WO) > 0 Then
WOCell.Interior.Color = RGB(255, 0, 0)

End If

Next WOCell

 

[jakel27]

I think I got it working by combining my 2 attempts together as so... (if anyone is interested or maybe can refine my work. I wasn't sure if I could have a FOR statement within my IF statement.

Sub Highlight_Cells_and_Copy()

'Highlight
Dim R As Range, WOCell As Range, datetoday As Range
Dim WO As String, Var As String
Set R = Range("C15000", Range("C15000").End(xlDown)) 'look at this range
Set datetoday = Range("E15000", Range("E15000").End(xlDown))

Columns(3).Interior.Color = xlNone

For Each Cell In datetoday

If Cell.Value = Date Then
Cell.Offset(0, -2).Interior.Color = RGB(255, 0, 0)
Var = Cell.Offset(0, -2).Value

For Each WOCell In R
If InStr(WOCell.Value, Var) > 0 Then
WOCell.Interior.Color = RGB(255, 0, 0)
End If
Next WOCell
End If

Next