VBA If cells ...

[Pinaceous]

Good Day,

In working with Range.H11:AC180, if any of these cells have the words "ENTER" in them, I'd like to make that cell highlighted red.

Any suggestions of vba or cf??


Thanks,
pinaceous

 

[Joe4]

Conditional formatting.

Just select you entire range and then use the Conditional Formatting formula:

=H11="ENTER"

And then choose your desired formatting.

 

[Pinaceous]

Hey Joe4,

I do appreciate that and due to the nature of the sheet could you kindly show me the vba method?

I'm experimenting with the following:

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "J").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "J").Interior.Color = vbRed Then Cells(i, "J").Value = "ENTER"
    Next

Please let me know, if you can help!

R/
pinaceous

 

[BigBeachBananas]

Hey Joe4,

I do appreciate that and due to the nature of the sheet could you kindly show me the vba method?

I'm experimenting with the following:

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "J").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, "J").Interior.Color = vbRed Then Cells(i, "J").Value = "ENTER"
    Next

Please let me know, if you can help!

R/
pinaceous

Isn't your logic flipped? Shouldn't you check if the cell contains "Enter" and then highlight red?

 

[Rick Rothstein]

Try turning the Macro Recorder on, do what @Joe4 told you , turn the Macro Recorder off and you will have the code to use.

 

[bebo021999]

Like this?

Option Explicit
Sub test()
Dim rng As Range, f, fCell As String
Set rng = ActiveSheet.UsedRange
Set f = rng.Find("ENTER", , xlValues, xlPart)
If Not f Is Nothing Then
    f.Interior.Color = vbRed
    fCell = f.Address
    Do
        Set f = rng.FindNext(f)
        If Not f Is Nothing Then f.Interior.Color = vbRed
    Loop Until f.Address = fCell
End If
End Sub

 

[eiloken]

In conditional formatting: =ISNUMBER(FIND("ENTER",H11))
In VBA:

Sub HightlightENTER()
    Dim xCll As Range
    For Each xCll In Range("H11:AC180")
        If InStr(xCll.Value, "ENTER") > 0 Then
            xCll.Interior.Color = RGB(255, 0, 0)
        Else
            xCll.Interior.Pattern = xlNone
        End If
    Next xCll
End Sub

 

[Pinaceous]

eiloken,

Okay, thank you! This is very interesting now!

How would you turn on/off the

 =ISNUMBER(FIND("ENTER KG!",H11))

conditional formating?

For example, if I wanted to run this CF on opening up my document then turn it off after it has been completed?

Or would this situation call for the sub approach?

Thanks,
-pinaceous

 

[eiloken]

eiloken,

Okay, thank you! This is very interesting now!

How would you turn on/off the

 =ISNUMBER(FIND("ENTER KG!",H11))

conditional formating?

For example, if I wanted to run this CF on opening up my document then turn it off after it has been completed?

Or would this situation call for the sub approach?

Thanks,
-pinaceous

You can set a cell to value that you can use it for turn on or off your hightlight condition. For example:
in conditional formatting: =AND($A$1="ON",ISNUMBER(FIND("ENTER",H11))) this formula will highlight your cells when cell A1 = ON
in vba:

Sub HightlightENTER()
    Dim xCll As Range
    Range("H11:AC180").Interior.Pattern = xlNone
    If Range("A1").Value = "ON" Then
        For Each xCll In Range("H11:AC180")
            If InStr(xCll.Value, "ENTER") > 0 Then
                xCll.Interior.Color = RGB(255, 0, 0)
            End If
        Next xCll
    End If
End Sub