Conditional formatting in VBA

[bloomingflower]

Hi,

I have one question: I am trying to figure out how to add to the middle of my recorded macro part that will take a kid name from user and mark it green in the whole spreadsheet. I have tried the code below, but it doesn't work (error object required).

Name = InputBox("Enter Kid's Name:")
Dim myrange As Range
Set myrange = Sheet1.Cells.Select
For Each cell In myrange.Cells
If cell.Value = Name Then
cell.Interior.Color = 4
End If
Next

 

[My Aswer Is This]

Try this:

Sub My_Name()
'Modified 3/9/2020 10:15:01 PM EST
Application.ScreenUpdating = False
Dim r As Range
Dim Name As String
Name = InputBox("Enter Kids name")
    For Each r In ActiveSheet.UsedRange
If r.Value = Name Then r.Interior.ColorIndex = 4
Next
Application.ScreenUpdating = True
End Sub

 

[rlv01]

One way.

Sub HighlightName()
    Dim Name As String

    Name = InputBox("Enter Kid's Name:")
    If Name <> "" Then
        SetCondition Name
    End If
End Sub

Sub SetCondition(Name As String)
    Dim myrange As Range
    Dim FC As FormatCondition

    Set myrange = Sheet1.UsedRange

    With myrange
        .FormatConditions.Delete                      ' clear existing format conditions
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=Name
        Set FC = .FormatConditions(1)
        With FC.Interior
            .Color = 12379352
            .TintAndShade = 0
        End With
        With FC.Font
            .Bold = True
            .Italic = False
            .TintAndShade = 0
        End With
    End With
End Sub

 

[Fazza]

A tiny variation to "My Aswer Is This"'s solution - to save time in case you have a huge amount of data.
This replaces reading cell values from the worksheet individually by reading them in one hit - and storing values in an array.

Sub My_Name()
    
    Dim i As Long, j As Long
    Dim ar As Variant
    Dim Name As String
    
    Application.ScreenUpdating = False
    
    Name = InputBox("Enter Kid's name")
    
    If Len(Name) > 0 Then
        
        If vbYes = MsgBox("Erase all existing colors?", vbDefaultButton2 + vbYesNo) Then Cells.Interior.Color = xlNone
        
        ar = ActiveSheet.UsedRange.Value
        For i = LBound(ar, 1) To UBound(ar, 1)
            For j = LBound(ar, 2) To UBound(ar, 2)
                If ar(i, j) = Name Then Cells(i, j).Interior.Color = vbGreen
            Next j
        Next i
        Erase ar
        
    End If

End Sub

 

[Fazza]

my earlier code assumed the used range was from cell A1.
if it isn't, the result will be wrong. here is a minor re-write to correct

Sub My_Name()

    Dim i As Long, j As Long
    Dim ar As Variant
    Dim Name As String

    Application.ScreenUpdating = False

    Name = InputBox("Enter Kid's name")

    If Len(Name) > 0 Then

        If vbYes = MsgBox("Erase all existing colors?", vbDefaultButton2 + vbYesNo) Then Cells.Interior.Color = xlNone

        With ActiveSheet.UsedRange
            ar = .Value
            For i = LBound(ar, 1) To UBound(ar, 1)
                For j = LBound(ar, 2) To UBound(ar, 2)
                    If ar(i, j) = Name Then .Cells(i, j).Interior.Color = vbGreen
                Next j
            Next i
            Erase ar
        End With
        
    End If

    Application.ScreenUpdating = True

End Sub

 

[bloomingflower]

Try this:

Sub My_Name()
'Modified 3/9/2020 10:15:01 PM EST
Application.ScreenUpdating = False
Dim r As Range
Dim Name As String
Name = InputBox("Enter Kids name")
    For Each r In ActiveSheet.UsedRange
If r.Value = Name Then r.Interior.ColorIndex = 4
Next
Application.ScreenUpdating = True
End Sub

thank you, it is perfect for my data

 

[bloomingflower]

my earlier code assumed the used range was from cell A1.
if it isn't, the result will be wrong. here is a minor re-write to correct

Sub My_Name()

    Dim i As Long, j As Long
    Dim ar As Variant
    Dim Name As String

    Application.ScreenUpdating = False

    Name = InputBox("Enter Kid's name")

    If Len(Name) > 0 Then

        If vbYes = MsgBox("Erase all existing colors?", vbDefaultButton2 + vbYesNo) Then Cells.Interior.Color = xlNone

        With ActiveSheet.UsedRange
            ar = .Value
            For i = LBound(ar, 1) To UBound(ar, 1)
                For j = LBound(ar, 2) To UBound(ar, 2)
                    If ar(i, j) = Name Then .Cells(i, j).Interior.Color = vbGreen
                Next j
            Next i
            Erase ar
        End With
       
    End If

    Application.ScreenUpdating = True

End Sub

I have problem with this one as it shows that object is missing... maybe there is something wrong with my excel

 

[bloomingflower]

One way.

Sub HighlightName()
    Dim Name As String

    Name = InputBox("Enter Kid's Name:")
    If Name <> "" Then
        SetCondition Name
    End If
End Sub

Sub SetCondition(Name As String)
    Dim myrange As Range
    Dim FC As FormatCondition

    Set myrange = Sheet1.UsedRange

    With myrange
        .FormatConditions.Delete                      ' clear existing format conditions
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:=Name
        Set FC = .FormatConditions(1)
        With FC.Interior
            .Color = 12379352
            .TintAndShade = 0
        End With
        With FC.Font
            .Bold = True
            .Italic = False
            .TintAndShade = 0
        End With
    End With
End Sub

Hi, unfortunately it shows me that object is missing. Thank you for your help, there is probably issue with my excel.

 

[rlv01]

I have problem with this one as it shows that object is missing... maybe there is something wrong with my excel

Which object was missing?

 

[Fazza]

Doe the (active) worksheet have some data on it? (If not, I guess there'd be no UsedRange. Which would give an error.)
Or maybe worksheet protection?