change cell font and fill colour by code

[palaeontology]

Hi,

I have a UserForm with a command button that, when pressed, causes page 1 and 2 of any worksheet named with a 5-digit number (eg: 76349) to be printed.

For Each ws In ThisWorkbook.Worksheets
    If ws.Name Like "#####" Then
        ws.PageSetup.Orientation = xlLandscape
        ws.PrintOut From:=1, To:=1
        ws.PageSetup.Orientation = xlLandscape
        ws.PrintOut From:=2, To:=2
    End If
Next ws

However, before the act of printing, I'd like the range of cells P22:U22 to have their font colour changed to white and any fill colour that might be in one or more of the cells to also be change to white so nothing in those cells will appear in the print.

I don't know if it's important, but some of the cells in that range are merged. Does that make a difference to the code ?

NOTE: After the sheet is printed, the sheet is deleted using an different code, so the font and fill colour does not need to be returned to their former colours.

Is someone able to tell me how to amend my current code to allow for this ?

 

[Rick Rothstein]

How were the cells in the Range P22:U22 colored... manually or via Conditional Formatting?

Do you want the font colors restored to black afterward?

Do you want any filled cells to have their color restored afterwards?

 

[palaeontology]

The cell fonts were coloured manually, however, if one of the cells has a fill colour, it's due to a code that fills a cell when double clicked.

This is the code I used to fill a cell when double clicked ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Range("P24:P29").Interior.ColorIndex = xlNone
If Not Intersect(Target, Range("P24:P29")) Is Nothing Then
Range("BF9").Value = Target.Value
Target.Interior.ColorIndex = 4
End If
If Not Intersect(Target, Range("Q24")) Is Nothing Then
UserForm2.Show
End If
End Sub

No, the sheets are deleted (using a different code) after printing, so none of the font or fill colour needs to be restored.

Kind regards,

Chris

 

[Rick Rothstein]

Here is your original code snippet from Message #1 ... I believe the code lines I added (shown in red) should do what you originally asked for.

For Each WS In ThisWorkbook.Worksheets
    If WS.Name Like "#####" Then
        [B][COLOR="#FF0000"]WS.Range("P22:U22").Font.Color = vbWhite[/COLOR][/B]
        [B][COLOR="#FF0000"]WS.Range("P22:U22").Interior.Color = vbWhite[/COLOR][/B]
        WS.PageSetup.Orientation = xlLandscape
        WS.PrintOut From:=1, To:=1
        WS.PageSetup.Orientation = xlLandscape
        WS.PrintOut From:=2, To:=2
    End If
Next WS

 

[palaeontology]

Rick, thankyou, so much, for putting that together for me.

It works a treat. Thankyou very much.

Very kind regards,

Chris