If cell is colored enter a letter to it

[kisinana]

I want to look at a column and if a cell has been highlighted by a certain color then place a letter N in that cell. Also if a different color is in the column then place a LS in that cell.
I am not sure what I am missing. It looks Ok in the module and does not flag until I try to run it.

If Range("F1:F101").Cell.Interior.ColorIndex = 4 Then
    Cell.Value = "H"
    If Cell.Interior.ColorIndex = 15 Then
        Cell.Value = "LS"
    End IF

[June7]

Are these cell colors set with Conditional Formatting? Possibly VBA cannot read color codes set by CF. Seems I read that somewhere.

I will have to do some testing.

Your code structure is not valid.

Confirmed, won't work with CF color setting.

Otherwise, consider:

Sub ChangeValueBasedOnCellColor()
    Dim rg As Range, xRg As Range
    Set xRg = Worksheets("Sheet1").Range("F1:F101")
    Application.DisplayAlerts = False
    For Each rg In xRg
        With rg
            Select Case .Interior.ColorIndex
                Case Is = 4
                    .Value = "H"
                Case Is = 15
                    .Value = "LS"
            End Select
        End With
    Next
    Application.DisplayAlerts = True
End Sub

[Aussiebear]

You might use this to determine the colour of a CF cell, but its currently only as a Message box output.

Sub CheckConditionalFormattingColor()
    Dim cell As Range
    Set cell = Range("A1") 
    ' Replace with the cell you want to check
    If cell.DisplayFormat.Interior.ColorIndex <> xlNone Then 
        ' Cell has a color applied (either directly or by Conditional Formatting)
        MsgBox "Cell " & cell.Address & " has color index: " & cell.DisplayFormat.Interior.ColorIndex
    Else
        MsgBox "Cell " & cell.Address & " has no color."
    End If
End Sub

[June7]

Adding DisplayFormat to code DOES detect CF colors.

Sub ChangeValueBasedOnCellColor()    
    Dim rg As Range, xRg As Range
    Set xRg = Worksheets("Sheet1").Range("F1:F101")
    Application.DisplayAlerts = False
    For Each rg In xRg
        With rg
            Select Case .DisplayFormat.Interior.ColorIndex
                Case Is = 4
                    .Value = "H"
                Case Is = 15
                    .Value = "LS"
            End Select
        End With
    Next
    Application.DisplayAlerts = True 
End Sub

[kisinana]

Hi June7
This works perfectly
Thank you very much.
I see where I went wrong and now I am just stepping through your example so I have a better understanding
Kisinana

[adhocchicken]

there are a few issues with your current approach:

1. Range("F1:F101").Cell is not correct. To loop through each cell in a range, you need to use a For Each loop.
   
2. The logic inside your If statements needs to be adjusted so that you're checking the color for each individual cell.
   
3. The Cell.Value = "LS" should be inside the second If statement, but it should only be executed when the color of the cell is 15.

[Aussiebear]

@adhochicken. I have removed your spam link in your signature. I would advise you not to spam again.