In Excel VBA, read a cell’s fill color with Range.Interior.Color; for example, Worksheets("Sheet1").Range("B2").Interior.Color returns its color as a numeric value. If conditional formatting may be changing the visible fill, use Range.DisplayFormat.Interior.Color instead. The right property depends on whether you want the cell’s directly assigned fill or the color Excel currently displays.
What VBA Means by a Cell’s Background Color
Excel’s object model calls a cell’s background fill its interior. The Interior property exposes that formatting, including its color and pattern. This is different from the font color, which you read with Range.Font.Color, and from a border color, which belongs to a cell’s Borders collection. If a macro reports an unexpected color, first check that it is reading the intended formatting property.
For a single cell, Interior.Color is usually the simplest choice. The returned value is a VBA Long representing a color, not a label such as “blue” or a web-style text value such as “#0000FF.” You can compare it with another color value, pass it to VBA’s RGB function, or convert its components if you need to display red, green, and blue numbers.
One important distinction is that a cell’s stored formatting is not always the same as its visible formatting. A conditional-formatting rule can make a cell appear red even though its ordinary interior fill is still white or has no fill. That difference explains why the two properties in the opening answer are both useful.
Read a Cell’s Fill with a Short Macro
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro below. Change the worksheet name and cell address to match your workbook. In the editor, press Ctrl+G to show the Immediate window, then run the macro with F5 while the cursor is inside it. The result appears in the Immediate window.
Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even when a macro seems to make only a small change.
Sub ShowCellFill()
' Read the direct fill from one cell.
Dim target As Range
Dim fillColor As Long
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2")
If target.Interior.Pattern = xlPatternNone Then
Debug.Print target.Address(External:=True) & " has no fill."
Else
fillColor = target.Interior.Color
Debug.Print target.Address(External:=True) & _
" color value: " & CStr(fillColor)
End If
End Sub
The pattern check matters because a cell with no fill and a cell filled with white can look identical against a white worksheet. The macro reports no fill separately instead of treating every possible numeric color result as proof that the cell has a visible background. If the target is filled, the value printed is the direct interior color.
How Excel Stores the Color Value
VBA’s RGB(red, green, blue) function creates the same kind of numeric color value used by the Color property. Each component ranges from 0 to 255. For instance, RGB(255, 0, 0) represents red, while RGB(0, 0, 255) represents blue. You can therefore compare a cell’s fill directly with a known RGB color, rather than trying to interpret an unexplained number.
For a cell at B2 on Sheet1, this comparison identifies a red fill:
If Worksheets("Sheet1").Range("B2").Interior.Color = RGB(255, 0, 0) Then
Debug.Print "The fill is red."
End If
When you need the components of a returned color, they can be extracted from the numeric value. With a filled single cell in B2 on Sheet1, the following VBA reads the color and separates its red, green, and blue components. The variable names make clear which component each calculation represents.
Sub PrintFillComponents()
' Split the cell's RGB color into its three components.
Dim target As Range
Dim fillColor As Long
Dim redPart As Long
Dim greenPart As Long
Dim bluePart As Long
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2")
fillColor = target.Interior.Color
redPart = fillColor Mod 256
greenPart = (fillColor \ 256) Mod 256
bluePart = (fillColor \ 65536) Mod 256
Debug.Print redPart, greenPart, bluePart
End Sub
These calculations are useful when a report needs separate channel values, but most macros do not need to unpack the number. Comparing Interior.Color with RGB is generally easier to read and less error-prone. A theme color may also be represented by a resolved color value; that value tells you the color currently associated with the cell, not necessarily which theme setting or tint produced it.
Handle Conditional Formatting Correctly
Conditional formatting applies appearance according to rules, such as highlighting a negative balance or marking duplicate values. In this situation, Interior.Color describes the cell’s ordinary formatting, while DisplayFormat.Interior.Color reads the effective fill after Excel applies conditional formatting. The DisplayFormat property is available in Excel 2010 and later, including Microsoft 365 desktop Excel.
For example, a macro can inspect the displayed fill of B2 on Sheet1 with this statement:
displayColor = Worksheets("Sheet1").Range("B2").DisplayFormat.Interior.Color
Declare displayColor as a Long before using that statement in a procedure. If the conditional rule changes the cell’s displayed fill, this is the property to check when your goal is to describe what a person sees. If you instead want to find the cell’s manually assigned fill regardless of rules, use Interior.Color.
There is a notable limitation: DisplayFormat is intended for VBA procedures, not as a reliable way to build a worksheet user-defined function that recalculates from conditional formatting. A custom function called from a worksheet cell may not return the displayed conditional format as expected. For a report, run a macro or another VBA procedure rather than relying on a worksheet formula wrapper.
Distinguish No Fill from a White Fill
A visually white cell does not necessarily have a white fill. It may have no fill at all, letting the worksheet background show through, or it may have a solid white fill applied. These states can matter when a macro clears formatting, copies a style, or tries to identify cells that a user deliberately colored.
Check Interior.Pattern against xlPatternNone to test for no fill. When the pattern is not none, Interior.Color gives the assigned color. This is more meaningful than treating a particular numeric color as a universal marker for blank formatting. A workbook can also use patterns, so the fill’s pattern and color are separate pieces of formatting information.
For conditional formatting, check the displayed pattern through DisplayFormat.Interior.Pattern if the question is whether the effective appearance has a fill. The direct Interior.Pattern property still concerns the underlying formatting. Keeping the “stored” and “displayed” distinction in mind avoids a common mistake: concluding that a cell has no color simply because the direct color does not match what appears on screen.
Check Several Cells Without Losing Context
For a range containing many cells, process each cell individually if you need a separate answer for every one. A multi-cell range can contain mixed formatting, so asking for one color for the entire range cannot describe each cell reliably. Looping through the cells also lets a macro report the address beside each result.
Save a copy before running this reporting macro, as with any VBA procedure. It reads cells B2:B10 on Sheet1 and prints either “no fill” or the direct color value for each cell in the Immediate window.
Sub ListCellFills()
' Report each cell's direct fill in the Immediate window.
Dim cell As Range
For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("B2:B10").Cells
If cell.Interior.Pattern = xlPatternNone Then
Debug.Print cell.Address(False, False), "no fill"
Else
Debug.Print cell.Address(False, False), cell.Interior.Color
End If
Next cell
End Sub
To inspect effective conditional-formatting fills in a loop, replace the Interior references with DisplayFormat.Interior. If you want the output in worksheet cells rather than the Immediate window, adjust the macro deliberately and test it on a copy: writing results changes the workbook, and VBA actions cannot be reversed with the ordinary Undo command.
Color and ColorIndex: Which Property to Use?
Interior.Color is usually the best property for modern workbooks because it works with RGB color values and makes direct comparisons straightforward. The older Interior.ColorIndex property returns a palette index or a special value, such as xlColorIndexNone, rather than the direct RGB number. It can be useful when maintaining older VBA that was built around Excel’s indexed color palette, but an index is less intuitive when you need to identify a specific modern color.
Do not assume that a ColorIndex number is a universal color name. Its meaning depends on Excel’s color palette, and it does not provide the same direct RGB-style comparison as Color. For new code, choose Color unless you specifically need compatibility with existing palette-based logic. Use ColorIndex only with a clear reason and test it against the workbooks your macro must support.
Also choose the property based on the question you are answering. To compare a manually set fill, use Interior.Color. To compare the color currently visible after conditional formatting, use DisplayFormat.Interior.Color. To determine whether the direct fill is absent, check Interior.Pattern. These are different questions, not interchangeable spellings of the same test.
Common Problems and Practical Checks
- The macro reports the wrong cell. Confirm the worksheet name and address in the
Set targetline. A fully qualified reference such asThisWorkbook.Worksheets("Sheet1").Range("B2")avoids relying on whichever sheet happens to be active. - The number does not look like a color code.
Interior.Colorreturns a numeric VBA color value, not a hex string or color name. Compare it withRGB, or split the value into its three components if needed. - The cell looks colored, but the reported direct fill is different. Check for conditional formatting and read
DisplayFormat.Interior.Colorfrom a VBA procedure. - A white cell is being mistaken for an empty one. Test
Interior.PatternforxlPatternNone; do not infer “no fill” from appearance alone. - The Immediate window appears empty. In the Visual Basic Editor, press Ctrl+G to show it, then run the procedure again. The macro uses
Debug.Print, which writes there rather than into the worksheet.
In Excel for Microsoft 365 on Mac, VBA is available, but keyboard shortcuts and editor access can differ from Windows; use the Visual Basic Editor command available in your Excel interface if Alt+F11 does not open it. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app to run these procedures. A workbook containing macros should be saved in a macro-enabled format such as .xlsm if you need to preserve its VBA code.
Frequently Asked Questions
How do I get the RGB values of a cell’s fill in VBA?
Read the cell’s Interior.Color into a Long, then extract red, green, and blue with the component calculations shown above. For a direct comparison, use Interior.Color = RGB(red, green, blue) instead of converting the value.
Why does Interior.Color not match the color I see?
A conditional-formatting rule may be changing the displayed fill. Compare DisplayFormat.Interior.Color in a VBA procedure to inspect the effective appearance, and use Interior.Color when you need the underlying fill formatting.
How can I tell whether a cell has no fill?
Check whether cell.Interior.Pattern = xlPatternNone. A white fill and no fill can look alike on a white sheet, so the color’s appearance alone is not a dependable test.
Can I use ColorIndex instead of Color?
Yes. Interior.ColorIndex is useful in older code that relies on Excel’s indexed palette, but it returns an index or a special value rather than a direct RGB color. For most new code, Interior.Color is clearer.
Can I use DisplayFormat in a worksheet formula?
Do not rely on it for a worksheet user-defined function. Use DisplayFormat from a VBA macro or procedure when you need the conditional-formatting appearance; worksheet functions may not return that effective formatting as expected.
Related
- Excel Shortcut to Insert a Cut Row
- Excel Shortcut to Insert Another Row in Windows and Mac
- Excel Shortcut to Select to the End of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Remove Duplicates Excel VBA Code: Safe ExamplesTo remove duplicate rows with Excel VBA, select the full table, including its header row, then run a…
- Excel VBA: Find All Cells With a ValueTo find every cell that matches a particular value in Excel VBA, use the Range.Find method and repeat…
- Access VBA Date Functions: Dates, Times, and ExamplesAccess VBA provides built-in date and time functions for getting the current date, creating date values, adding or…
- Excel VBA Activate Sheet: Syntax, Examples, and FixesTo activate a worksheet with VBA, reference it by name and call its Activate method: ThisWorkbook.Worksheets("Summary").Activate. The sheet…
- How to Find VBA Code in Excel: Open and Search MacrosTo find VBA code in Excel for Microsoft 365 on Windows, open the workbook in the desktop app,…
- Excel VBA Dictionary of Arrays: How to Store and Use DataYes—you can store arrays as values in an Excel VBA Dictionary. On Windows, create a Scripting.Dictionary, assign an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.