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:


Range("F1:F101").Cell is not correct. To loop through each cell in a range, you need to use a For Each loop.

The logic inside your If statements needs to be adjusted so that you're checking the color for each individual cell.

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.