ActiveCell.Interior.Color is NoFill but returning 16777215

[crookesa]

I have some cells that are formatted as the basis for conditional formatting.
My VBA code looks at those cells formats and assigns the values to variables
I look through each cell and apply the basis formatting to a range.

Some cells I have a set to No Fill and that's visible on the cell as I can see the grid lines.
When it assigns the value to a temp variable it uses 16777215 an when I apply that using conditional formatting it sets it to white.

Is this the correct behaviour or am I missing something?

I don't want to say if colour is 16777215 then make it xlNone as that may be the colour I actually want.

Sub get_duty_formatting()
    
    Worksheets("format_helper").Cells.Clear
    
    Worksheets("Duties").Range("sm_duties").Copy Worksheets("format_helper").Range("A1")
    Worksheets("Duties").Range("da_duties").Copy Worksheets("format_helper").Range("B1")
    Worksheets("Duties").Range("atco_duties").Copy Worksheets("format_helper").Range("C1")
    Worksheets("Duties").Range("non_operational_duties").Copy Worksheets("format_helper").Range("D1")
    
    Worksheets("Data Assistant").Activate
    Worksheets("Data Assistant").Range("B3:AG20").Select
    Selection.FormatConditions.Delete
    
    Dim NumRows As Integer, NumCols As Integer
    Dim x As Integer, j As Integer
    Dim cell_value As String, font_colour As Long, font_bold As Boolean, font_italics As Boolean, fill_colour As Long
    
    Sheets("format_helper").Visible = xlSheetVisible
    
    Sheets("format_helper").Activate
    Range("A1").Select
    
    'Set numrows = number of rows of data.
    'Set numcols = number of cols of data.
    NumRows = Range(ActiveCell, ActiveCell.End(xlDown)).Rows.Count
    NumCols = Range(ActiveCell, ActiveCell.End(xlToRight)).Columns.Count
    
    'Establish "For" loop to loop "numcols" number of times.
    For j = 2 To NumCols + 1
        'Establish "For" loop to loop "numrows" number of times.
        For x = 1 To NumRows
            cell_value = ActiveCell.Value
            Debug.Print cell_value
            font_colour = ActiveCell.Font.Color
            Debug.Print font_colour
            font_bold = ActiveCell.Font.Bold
            font_italics = ActiveCell.Font.Italic
            fill_colour = ActiveCell.Interior.Color
        
            If ActiveCell.Column = 2 Then
            
                Worksheets("Data Assistant").Activate
                
                With Worksheets("Data Assistant").Range("B3:AG20").FormatConditions.Add(xlCellValue, xlEqual, cell_value)
                    With .Font
                        .Color = font_colour
                        .Bold = font_bold
                        .Italic = font_italics
                    End With
                    With .Interior
                        .Color = fill_colour
                    End With
                End With

                Worksheets("format_helper").Activate
            End If
            
            'Selects cell down 1 row from active cell.
            ActiveCell.Offset(1, 0).Select
        Next
        Cells(1, j).Select
        
        If j <= NumCols Then
            NumRows = Range(ActiveCell, ActiveCell.End(xlDown)).Rows.Count
        End If
    Next j
    
    'Sheets("format_helper").Visible = xlSheetVeryHidden
    
End Sub

 

[mikerickson]

You are looking at the .Color propreties, not the .ColorIndex properties.
There is no xlNone .Color. The .Color property returns the color that would be seen. (Your computer is not transparent, therefore "no color" is not an option.)

So, what you see is white. and the RGB value for white is RGB(255, 255, 255) which equals 16777215

 

[crookesa]

Sorry you’re correct.
I had tried that but I believe I was limited to 56 colours with .ColorIndex

I made a cell red fill and changed the 2 following lines to and it didn't change the fill colour

fill_colour = ActiveCell.Interior.ColorIndex

With .Interior
    .ColorIndex = fill_colour
End With

The value was -4142

 

[mikerickson]

That is the value for xlNone, but when applied by Conditional Formatting, it does not change the native color of the cell.

 

[crookesa]

So if the cell was already xlNone (or red for example) then it stays that way if the value is -4142 otherwise it applies the color.
Would a solution be below?

If fill_colour = -4142 then
    With .Interior
        .ColorIndex = 0
    End With   
else
    With .Interior
        .ColorIndex = fill_colour
    End With
end if

 

[mikerickson]

What do you want conditional formatting to do?
If you want it to cause a particular fill color, use the .Color property.
If you want it to leave the fill color alone when the condition is met, then don't set any color value for the .FormatCondition

 

[Jon Peltier]

1. In general you should not be using .ColorIndex in Excel 2007 or later, except...
2. You can use .ColorIndex to find a cell with no fill color, and if .ColorIndex does not indicate it's got no fill, use .Color to get the actual color.
3. To get the conditionally formatted color, you need to insert .DisplayFormat

If ActiveCell.DisplayFormat.Interior.ColorIndex = xlNone then
    ' No Fill
Else
    ' RGB color
    FillColor = ActiveCell.DisplayFormat.Interior.Color
Endif

 

[crookesa]

What do you want conditional formatting to do?
If you want it to cause a particular fill color, use the .Color property.
If you want it to leave the fill color alone when the condition is met, then don't set any color value for the .FormatCondition

I am taking a range of cells and looping through them getting their font colour and fill colour and then applying those values to a separate bigger range of cells using conditional formatting.
So a user just has to update one cell with required formatting and when they run the macro it'll apply that format along with other formats to a number of ranges with conditional formatting

My problem is that if the cell has no fill applied to it, that value is stored in the variable but when it applies the conditional value to the bigger range it fills the cell as white.

 

[crookesa]

1. In general you should not be using .ColorIndex in Excel 2007 or later, except...
2. You can use .ColorIndex to find a cell with no fill color, and if .ColorIndex does not indicate it's got no fill, use .Color to get the actual color.
3. To get the conditionally formatted color, you need to insert .DisplayFormat

If ActiveCell.DisplayFormat.Interior.ColorIndex = xlNone then
    ' No Fill
Else
    ' RGB color
    FillColor = ActiveCell.DisplayFormat.Interior.Color
Endif

Thanks for that, I'll try that when I'm back in work tomorrow

 

[crookesa]

1. In general you should not be using .ColorIndex in Excel 2007 or later, except...
2. You can use .ColorIndex to find a cell with no fill color, and if .ColorIndex does not indicate it's got no fill, use .Color to get the actual color.
3. To get the conditionally formatted color, you need to insert .DisplayFormat

If ActiveCell.DisplayFormat.Interior.ColorIndex = xlNone then
    ' No Fill
Else
    ' RGB color
    FillColor = ActiveCell.DisplayFormat.Interior.Color
Endif

Just looking at this now and I don't need to get the conditionally formatted colour. The cells I am using as a reference are manuay filled, coloured and styled by the user via the toolbar.
They do this to the ranges I initially copy to the format_helper worksheet. This worksheet now holds the values and formatting I require to be applied to the range on the "Data Assistant" worksheet I activate next.

I then loop through each column on the format_helper worksheet and get formatting of those cells (in my example above I am only concerned about column 2 as that represents my "Data Assistants"). I store the formats in each of the variables using the following commands.

cell_value = ActiveCell.Value
            Debug.Print cell_value
            font_colour = ActiveCell.Font.Color
            Debug.Print font_colour
            font_bold = ActiveCell.Font.Bold
            font_italics = ActiveCell.Font.Italic
            fill_colour = ActiveCell.Interior.Color

I then use my with statement and apply the conditional formatting based on cell_value and stored formatting variables to that range.

My problem is and I still haven't fixed it is. That if the user has formatted the fill colour as no fill from the drop down menu then the value my VBA code finds is 16777215 which is white (also tested by choosing white and no fill) how do I now the difference.

When the conditional formatting is applied the white does make a difference to the presentation rather than no fill as the user has applied.

Hope i'm explaining my problem ok and not repeating myself.