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]

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.

With ActiveCell.Interior
     If .ColorIndex = xlNone Then
        MsgBox "ActiveCell has no fill color set, looks white"
    ElseIf .Color = vbWhite Then
        MsgBox "Active Cell fill is set to white"
    Else
        MsgBox "Active Cell fill is set to not white"
    End If
End With

 

[Jon Peltier]

Let me adjust my previous code snippet.

UserFormattedCell is the cell that the user applied formatting to
TargetCell is the cell that you want to apply the user's formatting to
FillColor is a Long that contains the formatted color
ApplyColor is a boolean, which if true means a color should be applied to the target cell

First you need to get the color from the user-formatted cell:

If UserFormattedCell.Interior.ColorIndex = xlNone then
    ' No Fill
    ' Don't apply any fill color
    ApplyColor = False
Else
    ' RGB color
    FillColor = UserFormattedCell.Interior.Color
    ' Apply this fill color to the applicable cells
    ApplyColor = True
Endif

Then you need to format the target cell:

With TargetCell.Interior
    If ApplyColor Then
        .Color = FillColor
    Else
        .Pattern = xlNone
    End If
End With

 

[crookesa]

Thanks to both @Jon Peltier & @mikerickson. I created a variable nofill_flag of type boolean

If ActiveCell.Interior.ColorIndex = xlNone Then
    nofill_flag = True  
Else
    nofill_flag = False
End If

fill_colour = ActiveCell.Interior.Color

Then in the code I where I want to apply to conditional formatting I use

With .Interior
    If nofill_flag = True Then
        .ColorIndex = 0
    Else
        .Color = fill_colour
    End If
End With

Maybe I missed something also but when I apply the conditional formatting fill of xlNone if the user has selected a fill colour on that cell shoud that not break through?
I have applied the conditional formatting and manual then filled a cell which should have xlNone applied to it but the fill colour doesn't break through. If I remove the conditional formatting it breaks through.

Have I missed something on the way I'm applying the conditional formatting. I've tried it on a new sheet manually and it works as I have expected it to.

 

[RoryA]

No. Any fill you apply using CF, including actively applying No Fill, will override whatever the user does. If you don't want that, then don't modify the Interior property at all in your CF if the original was no fill.

 

[crookesa]

No. Any fill you apply using CF, including actively applying No Fill, will override whatever the user does. If you don't want that, then don't modify the Interior property at all in your CF if the original was no fill.

Thanks @RoryA for that, I commented out .ColorIndex = 0 and that's done the trick.

Thanks to all for the help.

 

[RoryA]

Glad we could help. To be fair it is confusing, but it is also consistent with what happens if you actually manually select No Fill in the CF dialog.