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]

thank you, it is perfect for my data

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[rlv01]

Hopefully this will address the "object missing" issue

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

    Set myrange = ActiveSheet.UsedRange

    Name = InputBox("Enter Kid's Name:")
    If Name <> "" Then
        If ActiveSheet.UsedRange.Cells.Count = 1 Then    'no data on active sheet, so add some for demonstration purposes
            Set myrange = ActiveSheet.Range("A1:Z50")
            myrange.Range("A1").Value = Name
        End If

        With myrange
            If .FormatConditions.Count > 0 Then
                .FormatConditions.Delete              ' clear existing format conditions
            End If
            .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 If
End Sub