[SOLVED] VBA - Excel - how to extract the default font color

[essaytee]

In Access VBA when creating Excel spreadsheets, how do I extract the default font color?

I have a routine where I cycle through a recordset and one particular field has one of three possible values, a positive number, zero or negative number, that are text fields, not numbers. (It's a leaderboard table and this field indicates the position move of the person from the previous leaderboard). I know how to set the color of the relevant cell, but I don't know how to refer to the default font color (other than not referring to it and obviously the default font color is set). For fields I'd rather set the color directly, after the color is determined, rather than continual if/else routines.

I'm setting the color like so:

Sheet1.Range(strRange).Font.Color = vbBlue

In my Googling, I did see references to Conditional formatting, and I'll certainly look at that afterwards, but at the moment I'd like to know how to directly refer to the default font color.

 

[arnelgp]

i think, the default color of a cel (range)

colorValue = Sheet1.Range(strRange).Interior.Color

 

[The_Doc_Man]

According to what I have researched, there is a default THEME for which ALL of the colors in that theme's parts are defaults for whatever feature they represent in the theme. I'm not sure there is a singular answer to what you asked.

 

[essaytee]

Thanks all for the responses. As usual, I overthink things. My executive decision is to simply refer to Black as my default colour, which it is anyway. I am the one creating the spreadsheet and then distributing it. It's not the case of me tinkering with somebody else's spreadsheet.

 

[Isaac]

In Access VBA when creating Excel spreadsheets, how do I extract the default font color?

I have a routine where I cycle through a recordset and one particular field has one of three possible values, a positive number, zero or negative number, that are text fields, not numbers. (It's a leaderboard table and this field indicates the position move of the person from the previous leaderboard). I know how to set the color of the relevant cell, but I don't know how to refer to the default font color (other than not referring to it and obviously the default font color is set). For fields I'd rather set the color directly, after the color is determined, rather than continual if/else routines.

I'm setting the color like so:

Sheet1.Range(strRange).Font.Color = vbBlue

In my Googling, I did see references to Conditional formatting, and I'll certainly look at that afterwards, but at the moment I'd like to know how to directly refer to the default font color.

Programmatically add some text and then read what its current font color is and that would be the default right?

 

[essaytee]

Programmatically add some text and then read what its current font color is and that would be the default right?

Thanks, simple, why didn't I think of that.

 

[essaytee]

Using ChatGPT I came across this solution. I haven't tried it yet but I assume it would do the trick.

Dim defaultColor As Long
defaultColor = ActiveWorkbook.Styles("Normal").Font.Color
MsgBox "Default Font Color: " & defaultColor

 

[The_Doc_Man]

@Isaac - very practical suggestion. Why search a complex topic when a simple and fast experiment would answer the question?

 

[Edgar_]

The default color is 0.

More on colors:
You can get and set a palette of colors using ThisWorkbook.Colors(index), where index is a number from 1 to 56. If you have not set them, you can use the colors loaded by default in there, they are bright and nice. You can view the colors with something like this (but any loop from 1 to 56 would do it):

Sub setColors()
    Dim i As Long, j As Long, counter As Long
    counter = 1
    For i = 1 To 8
        For j = 1 To 7
            Cells(i, j).Interior.Color = ThisWorkbook.Colors(counter)
            Cells(i, j) = counter & ", " & ThisWorkbook.Colors(counter)
            Cells(i, j).RowHeight = 50
            counter = counter + 1
        Next j
    Next i
End Sub

There are a lot of nicely named colors that you can access in VBA by simply using Excel.rgb and intellisense will show you a big list of them. For example, Excel.rgbBisque. You can also access them using xlrgbcolor, like xlrgbcolor.rgbBisque. Here's a picture of a part of the list:

The color selector shows you colors depending on the theme you have selected and they are workbook-scoped.

The themes are files in an Office directory, you can change themes programmatically using ThisWorkbook.Theme.ThemeColorScheme.Load thefilename

If you record a macro while changing themes, you'll get the folder where the themes are stored. Using that folder name, you could see what colors are loaded for your theme programmatically doing something like this:

Sub PaintCellsWithThemeColors()
    Dim folder As String
    folder = "C:\Program Files (x86)\Microsoft Office\Document Themes 16\Theme Colors" 'record a macro to get your path
 
    Dim counter As Long
    counter = 1
 
    Dim file As Object, i As Long
    For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(folder).Files
        ThisWorkbook.Theme.ThemeColorScheme.Load folder & "\" & file.Name
        Cells(counter, 1) = file.Name
        For i = 1 To 12
            With ThisWorkbook.Theme
                Cells(counter, i + 1) = .ThemeColorScheme(i)
                Cells(counter, i + 1).Interior.Color = .ThemeColorScheme(i)
                Cells(counter, i + 1).RowHeight = 50
            End With
        Next i
        counter = counter + 1
    Next file
End Sub

The code above will paint your first cells in a worksheet with the colors of the theme. The magic number 12 is the count of enums available for the ThemeColorScheme object.

 

[The_Doc_Man]

When dealing with Excel colors AND Access colors, be careful. This happened about 10 years ago, but I started using some of the Excel colors and it turns out that there is a different order of appearance for those guys. It's a case of "big endian" vs. "little endian." The link is to a post in a thread that discusses Excel and Access colors.

RGB to Access conversion formula

Ive been recently experimenting with changing control colours via vba (mostly for a menu/submenu, i have it colour coded, so when a user clicks a button it brings up a differently coloured submenu) the problem is when i change a backcolour im very limited in the colours i choose im using...

www.access-programmers.co.uk

Before anyone asks, YES that is confirmed.

Why are Excel RGB values backwards?

This function is an example. Note that the RGB values are actually BGR values. Why does excel do this? Function GetRGB(ByVal cell As Range) As String Dim R As String, G As String Dim b As St...

stackoverflow.com