VBA - Change Font Color to Match Background Color Based on Another Cell's Value

[singincpa]

Hi,

I'm trying to set up a macro (attached to a button) that will do two things when clicked:

- change the print area
- change the font color in certain cells to match their background color.

I have the code for changing the print area, but don't know how to get the other part. Basically, if cell AC1 is not blank, I want the font in multiple cell ranges (D8:D12, D14:D18, I8:I12, I14:I18, etc.) to change to match the background color so that they aren't visible when printed. Right now I have those cells conditionally formatted so that the font turns white if AC1 is not blank, but of course that only works if the cell background is white. The users of the spreadsheet have started changing the background color on those cells on occasion.

Any assistance would be greatly appreciated!

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim rng As Range
    If Range("AC1") <> "" Then
        For Each rng In Range("D8:D12, D14:D18, I8:I12, I14:I18")
            rng.Font.ColorIndex = rng.Interior.ColorIndex
        Next rng
    End If
    Application.ScreenUpdating = True
End Sub

 

[singincpa]

Thanks for this, however it still isn't working.

 

[Fluff]

Have you removed the conditional format?
If so please define

it still isn't working

 

[singincpa]

Yes I removed the conditional formatting. When I place an X in cell AC1 and then run the macro, the font colors are not changing.

 

[Fluff]

Do you mean the font colour doesn't change at all, or it changes, but not to the same colour as the fill?
If the latter, try this mod to mumps code

Sub Test()
    Application.ScreenUpdating = False
    Dim rng As Range
    If Range("AC1") <> "" Then
        For Each rng In Range("D8:D12, D14:D18, I8:I12, I14:I18")
            [COLOR=#ff0000]rng.Font.Color = rng.Interior.Color[/COLOR]
        Next rng
    End If
    Application.ScreenUpdating = True
End Sub