CODEWITHSUNDEEP
vbaexcel
Raul Gonzales
Raul Gonzales

Reputation: 906

Leave cell formatting intact

I have got this code:

With .Cells(i, 4)
    If .Value > 0.8 Then
        .Interior.Color = RGB(237, 67, 55) '<-- Red color
        .Font.Color = vbWhite

        ElseIf .Value > 0.6 Then
            .Interior.Color = RGB(255, 190, 0) '<-- Amber Colour
            .Font.Color = vbWhite

        ElseIf .Value2 = "---" Then
            .Interior.Color = .Interior.Color

        Else
            .Interior.Color = RGB(50, 205, 50) '<-- Green color
            .Font.Color = vbWhite
    End If
End With

The worksheet is already formatted to have alternating row colours by default but pending on the values of the cell the interior cell colour needs to change pending on the value of the cell but, some cells don't have numerical data and just "---". When this is the case I want the original formatting to remain as it was before the code was run.

Basically, If the cell contains "---" don't assign any other interior colour, but keep the colour already assigned.

Upvotes: 0

Views: 52

Answers (1)

tjb1
tjb1

Reputation: 757

I tested this and it seemed to work on my test data, for some reason excel is treating "---" as a value larger than 0.8 so moving the check for "---" first will stop this.

With .Cells(i, 4)
    If .Value = "---" Then
            .Interior.Color = .Interior.Color

        ElseIf .Value > 0.8 Then
            .Interior.Color = RGB(237, 67, 55) '<-- Red color
            .Font.Color = vbWhite

        ElseIf .Value > 0.6 Then
            .Interior.Color = RGB(255, 190, 0) '<-- Amber Colour
            .Font.Color = vbWhite

        Else
            .Interior.Color = RGB(50, 205, 50) '<-- Green color
            .Font.Color = vbWhite

    End If
End With

Upvotes: 1

Related Questions