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

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

"Sandy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

On Jun 28, 8:33 am, "Sandy" <sandy_step...@DELETEhotmail.com> wrote:
> 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

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.

"Sandy" wrote:

> 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
>
>
>

"" & 0 are the same, try

If Len(Trim(mycell)) > 0 Then

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Sandy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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

"Sandy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

Glad to help

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Sandy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks all, Don's suggestion is good for what I want.
> Sandy
>
> "Sandy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
>>
>
>