Excel VBA: If cell contains different text in 2 different columns

[KC E]

Hello,

I'm using Excel 2013 to try to color a row gray if column F contains the phrase 'Existing Home Component Set' AND if column H contains the year 2017, the dates in column H are in the format mm/dd/yyyy.

1) I tried changing the SrchRng to equal Range("F2:H" & lastrow), but it does not highlight the rows containing either of those criteria. In other words, no row gets colored.

2) I also tried adding an AND statement with another InStr to search for "2017" like so:

InStr(1, cel.Value, "Existing Home Component Set") > 0

but I saw how that would not work and nothing happened.

I haven't used InStr before to look for two criteria and wondered if someone could help. I don't see anything like it on the net.

Thank you in advance.

Sub colorNodeMoveRowNCMS()

Dim SrchRng As Range, cel As Range, colorrng As Range
Dim lastrow As Integer

'use column C to just get row count
lastrow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

'***LOOK in Column F to see if it contains text 'Existing Home Component Set' and look in column H to see if it contains the year 2017***

Set SrchRng = Range("F2:F" & lastrow)
Set colorrng = Range("A2:H" & lastrow)

For Each cel In SrchRng
    If InStr(1, cel.Value, "Existing Home Component Set") > 0 Then
        'highlight that row gray
        cel.EntireRow.Interior.Color = RGB(208, 206, 206)
    End If
Next cel
End Sub

 

[Chihiro]

Try this... assuming column H contains date value.

Sub colorNodeMoveRowNCMS()

Dim SrchRng As Range, cel As Range, colorrng As Range
Dim lastrow As Integer

'use column C to just get row count
lastrow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

'***LOOK in Column F to see if it contains text 'Existing Home Component Set' and look in column H to see if it contains the year 2017***

Set SrchRng = Range("F2:F" & lastrow)
Set colorrng = Range("A2:H" & lastrow)

For Each cel In SrchRng
    If InStr(1, cel.Value, "Existing Home Component Set") > 0 And Year(cel.Offset(, 2).Value) = 2017 Then
        'highlight that row gray
      cel.EntireRow.Interior.Color = RGB(208, 206, 206)
    End If
Next cel
End Sub

Note that in Excel, dates are actually stored as double data type and displayed in format you specify. Therefore, Instr function will not work directly. You can use Range.Text method to do text comparison, but it is far safer to use .Value (or even better, .Value2) and treat it appropriately.

 

[KC E]

Try this... assuming column H contains date value.

Sub colorNodeMoveRowNCMS()

Dim SrchRng As Range, cel As Range, colorrng As Range
Dim lastrow As Integer

'use column C to just get row count
lastrow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row

'***LOOK in Column F to see if it contains text 'Existing Home Component Set' and look in column H to see if it contains the year 2017***

Set SrchRng = Range("F2:F" & lastrow)
Set colorrng = Range("A2:H" & lastrow)

For Each cel In SrchRng
    If InStr(1, cel.Value, "Existing Home Component Set") > 0 And Year(cel.Offset(, 2).Value) = 2017 Then
        'highlight that row gray
      cel.EntireRow.Interior.Color = RGB(208, 206, 206)
    End If
Next cel
End Sub

Note that in Excel, dates are actually stored as double data type and displayed in format you specify. Therefore, Instr function will not work directly. You can use Range.Text method to do text comparison, but it is far safer to use .Value (or even better, .Value2) and treat it appropriately.

Thank you, Chihiro. I didn't think about using Offset. It works very well. Thank you so much for your help.