CODEWITHSUNDEEP
excelvba
wilberox
wilberox

Reputation: 193

How to change value of cell in excel with no fill

I have a spreadsheet in excel where colored cells contain values, and cells with no fill are empty. I would like all cells with no fill color to have value 0. I have code that I have used to give black and yellow cells values 1 and 2 respectively.

Sub changeValuesBasedOnColour()

    Dim rg As Range
    Dim xRg As Range
    Set xRg = Selection.Cells
    Application.DisplayAlerts = False
    For Each rg In xRg
        With rg
            Select Case .Interior.Color
                Case Is = 0   'Black
                    .Value = 1
                Case Is = 65535 'Yellow
                    .Value = 2
                Case Is = xlNone
                    .Value = 0
                
             End Select
        End With
    Next
    Application.DisplayAlerts = False

End Sub

I have edited my code that I used to change value of cells based on fill color to include this section:

 Case Is = xlNone
   .Value = 0

However there is no change in my spreadsheet. How can I edit my code to refer to the cells with no fill color?

Upvotes: 1

Views: 1979

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

Color Does Not Distinguish NoFill From White!?

Since vbWhite or 16777215 used with the Color property will allow to write to white and not filled cells, I would suggest you use the ColorIndex property.

Select Case .Interior.ColorIndex
    Case Is = 1 ' Black
        .Value = 1
    Case Is = 6 ' Yellow
        .Value = 2
    Case Is = xlNone ' No color
        .Value = 0
     Case Else
End Select

or:

If .Interior.ColorIndex = xlNone Then
    .Value = 0          ' No Fill
Else
    Select Case .Interior.Color
        Case Is = 0     ' Black
            .Value = 1
        Case Is = 65535 ' Yellow
            .Value = 2
        Case Else
    End Select
End If

Upvotes: 0

BigBen
BigBen

Reputation: 49998

Use vbWhite or 16777215.

xlNone is applicable to the cell's .Interior.ColorIndex, which is not the same as .Interior.Color.

Upvotes: 2

Related Questions