Change Font Color with VBA

[Tuckejam]

Im having in issue with the font color in VBA that I have never run into before.

Using Excel 10

I have cell that are filled in with colors

It is Aqua Accent 5 40% Lighter. ( at least thats what pops up when i move my mouse to that color on the fill drop down box from the main ribon, it is the middle of the 5 Light blue stack second from the right in that drop down box)

Any way when i record the macro to change the color of the font in the cell to that same color (from black so text in that box wont be visable)

the macro records to change the color to Aqua Accent 5
but no color or shade information (this is the color that is on top of that 5 block stack of blue in the drop down box)

the macro that is recorded is

Sub Macro3()
'
' Macro3 Macro
'
'
    Range("N42:Z43").Select
    With Selection.Font
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        
    End With
    Range("AG38").Select
End Sub

it shows tint and shade 0.3999ect but how do i translate that into 40%lighter.

also i will be doing this for many differetn colors so i need to know how to fix it in general please so while i always greatfull for the help i get here I will need more than just this colors "code" i will need to know how to find the color codes my-self

Thanks again for all the great help i get here

 

[skywriter]

So you are recording a macro to change the font color to match the background color so that the font becomes invisible?
My question would be if so, did the recording of the macro produce the desired results, the font disappeared into the background? Color pallet for background might not match the font in name, but if the font disappears into the background then what's the issue?

 

[Tuckejam]

Umm... So No the macro did not produce the desired result. The backround color of the cell is

Aqua, Accent 5, Lighter 40% (that is what pops up when I move the mouse pointer over the color in the drop down box.)

When I recorded the macro to change the color of the font I selected

Aqua, Accent 5, Lighter 40%

and during the recording proccess the font does change to - Aqua, Accent 5, Lighter 40%

However then when i change the font back to Black and run the macro, the macro changes the font to

Aqua, Accent 5 NOT

Aqua, Accent 5, Lighter 40%

So I would say that when I run the macro to change the font color even though it does change it to the correct color, its about 40% darker (just my best guess) than the cell.

Which is still visable.

I know the easy fix would be to change the fill color of the cell to the standard

Aqua, Accent 5

that way when the macro runs it will match the cell. but....

A. That is not an option as i have many colors representing a lot of sub departments and this is a problem for most of them. and in addition this color has represented this sub department for some time now and it will confuse many people if i change its standard color.

And

B. I would like to know why the macro is not doing what was recorded and i would like to learn how solve this problem as i am currently trying learn as much VBA as possilbe.

Thanks again for the help though

 

[VoG]

Any good?

With Range("N42:Z43")
    .Font.Color = .Interior.Color
End With

 

[RickXL]

You could use RGB values.

When you pick the color for something, in the color palette you can select the "More Colors ..." button. In there you will see what the RGB values are set to.

You implement it like this:

Sub xx()
    Dim myColor As Long
    myColor = RGB(255, 128, 0)
    With Sheet1
        With .Range("E19")
            .Interior.Color = myColor
            .Font.Color = myColor
        End With
        .Shapes("Oval 1").Fill.ForeColor.RGB = myColor
    End With

End Sub

</pre>