VBA formula to highlight row if a cell in that row contains specific text?

[lichldo]

Hello, I am trying to write a VBA code to highlight a row in blue if any cell in that row contains the word "Scoping" (please note that there would likely be other text in that cell as well, it wouldn't be that word on it's own) in a range of A2:O500

I understand this can be done with conditional formatting, but I am building this as a templated macro to share with others and use on different sheets, so it's really easiest for us to do this as VBA

Is this possible?

 

[Joe4]

I understand this can be done with conditional formatting, but I am building this as a templated macro to share with others and use on different sheets, so it's really easiest for us to do this as VBA

If you know how to do it with Conditional Formatting, then you can just use VBA to apply that Conditional Formatting.
If you aren't sure what that code would look like, turn on the Macro Recorder, and record yourself applying the Conditional Formatting to one of the sheets.
That should give you most of the VBA code you need (and then you can just generalize it to make it more dynamic).

If you need help with "tweaking" or "cleaning up the code", post the code here with the details of what changes you want to make to it.

 

[lichldo]

If you know how to do it with Conditional Formatting, then you can just use VBA to apply that Conditional Formatting.
If you aren't sure what that code would look like, turn on the Macro Recorder, and record yourself applying the Conditional Formatting to one of the sheets.
That should give you most of the VBA code you need (and then you can just generalize it to make it more dynamic).

If you need help with "tweaking" or "cleaning up the code", post the code here with the details of what changes you want to make to it.

I have a number of conditional formatting formulas that work, but when I set them up with record macro on, it doesn't actually capture any data. So I am really struggling to figure out how to get it to VBA code.

These are what I have -

Applied to range $A$2:$O$1000

• =SEARCH("purchase",$G2)
• =SEARCH("scoping",$G2)
• =SEARCH("non-chargeable",$G2)

Applied to range $K$1:$L$1000

• =$K1<>$L1

Applied to range $M$1:$N$1000

• =$M1<>$N1

 

[Joe4]

In your original post, you said:

I am building this as a templated macro to share with others and use on different sheets, so it's really easiest for us to do this as VBA

So, do you want to apply this to ALL of the sheets in the workbook?
Or just specific ones?
If just specific ones, what is the logic of which ones to apply it to and which ones not to apply it to?

Also, with your 5 Conditioanl Formatting rules, what is the desired formatting for each one?
Are they all using the same color, or different ones (if different, please specify)?

 

[lichldo]

In your original post, you said:

So, do you want to apply this to ALL of the sheets in the workbook?
Or just specific ones?
If just specific ones, what is the logic of which ones to apply it to and which ones not to apply it to?

Also, with your 5 Conditioanl Formatting rules, what is the desired formatting for each one?
Are they all using the same color, or different ones (if different, please specify)?

There will only be 1 sheet in the workbook so it just needs to apply to that one as it is the whole workbook

Yes sorry I forgot to specify the formatting, I added below

Applied to range $A$2:$O$1000 (formatting is light blue fill for all 3)

• =SEARCH("purchase",$G2)
• =SEARCH("scoping",$G2)
• =SEARCH("non-chargeable",$G2)

Applied to range $K$1:$L$1000 (formatting is light red fill)

• =$K1<>$L1

Applied to range $M$1:$N$1000 (formatting is light red fill)

• =$M1<>$N1

 

[Joe4]

So, it appears that your last two overlap ranges with the first two (columns K:L overlap with A:O and columns M:N overlap with A:O).
In situation where BOTH conditions are met (i.e. if "scoping" is found in cell G4 and K4 <>L4), then who wins? What color should K4:K4 be, light blue or light red?

 

[Joe4]

Here is what the VBA code would structured like (you can adust the color codes, if you like):

Sub MyCFMacro()

'   Condition 1
    With Range("G2:O1000")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=IFERROR(SEARCH(""purchase"",$G2),0)+IFERROR(SEARCH(""scoping"",$G2),0)+IFERROR(SEARCH(""non-chargeable"",$G2),0)>0"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 15773696
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
    
'   Condition 2
    With Range("K1:L1000")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$K1<>$L1"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 8420607
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

'   Condition 3
    With Range("M1:N1000")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=$M1<>$N1"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 8420607
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub

Depending on the answer to the previous question, it may require a small tweak.