Cell colour doesn't delete

[Sandy]

I have the folloing code, why does the cell color in cell C10 not delete
when the value of cell C8 is ""?
Thanks Sandy

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False

Application.EnableEvents = False

For Each mycell In Range("C8:K8,M8:U8")
With mycell
If mycell.Value = "" Then
.Interior.ColorIndex = 36
.Font.ColorIndex = 1
.BorderAround LineStyle:=xlContinuous, Weight:=xlThin,
ColorIndex:=11
ElseIf mycell.Value <> "" Then
.Interior.ColorIndex = 11
.Font.ColorIndex = 45
.Borders.LineStyle = xlNone
End If
End With

With mycell.Offset(2)
If mycell.Value = 0 Then
.Interior.ColorIndex = 11
.Value = ""
ElseIf mycell.Value > 0 Then
.Interior.ColorIndex = 36
.Font.ColorIndex = 1
ElseIf mycell.Value = "" Then
.Interior.ColorIndex = xlColorIndexNone
.Value = ""
End If
End With
Next mycell

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub

 

[Peter T]

If mycell.Value = 0 Then

ElseIf mycell.Value = "" Then

An empty cell equals both 0 and "" so in your code the test for empty
doesn't get processed.

In passing, I would suggest only change formats if they need changing,
particularly Font. Also test your target range for relevant changed cell(s)
and only process if need to.

Changing formats (except clear formats) does not trigger events so no need
to disable events.

Regards,
Peter T

 

[Edward]

I have the folloing code, why does the cell color in cell C10 not delete
when the value of cell C8 is ""?
Thanks Sandy

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Application.ScreenUpdating = False

Application.EnableEvents = False

For Each mycell In Range("C8:K8,M8:U8")
With mycell
If mycell.Value = "" Then
.Interior.ColorIndex = 36
.Font.ColorIndex = 1
.BorderAround LineStyle:=xlContinuous, Weight:=xlThin,
ColorIndex:=11
ElseIf mycell.Value <> "" Then
.Interior.ColorIndex = 11
.Font.ColorIndex = 45
.Borders.LineStyle = xlNone
End If
End With

With mycell.Offset(2)
If mycell.Value = 0 Then
.Interior.ColorIndex = 11
.Value = ""
ElseIf mycell.Value > 0 Then
.Interior.ColorIndex = 36
.Font.ColorIndex = 1
ElseIf mycell.Value = "" Then
.Interior.ColorIndex = xlColorIndexNone
.Value = ""
End If
End With
Next mycell

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub

What do you mean by cell color deleting? Your code changes the color
to 36 (a light yellow) and this works.

Edward

 

[Guest]

Hi Sandy. You may also have problems deleting cell colors if the color was
set by conditional formatting. You have to include the FormatCondtions
property in your delete statement. See FormatConditions in VBA help.

 

[Don Guillett]

"" & 0 are the same, try

If Len(Trim(mycell)) > 0 Then

 

[Sandy]

Thanks all, Don's suggestion is good for what I want.
Sandy

 

[Don Guillett]

Glad to help