Use VBA to replace conditional formatting

Question

Hi,

I have a spread sheet that genertates a number of reports from the data that is inserted.  I have noticed that as the reports grow so does my file size.  After much testing I have determined that the huge file size that I am now looking at is due to the conditional formatting rules. 

The baseline data has headings that are used as titles in the reports, so there is a conditional format with reads: =$A17="Header" which results in the header line formatting to black background and white text.  Yes this could be done manually but there is a need to irradicate the train of thought 'oh I'm going to do the headings in blue not black'  I need uniformity.

Ultimately I would like to have a macro that would loop through the 500 lines of the data, locate the header lines with either and IIf (OR) statement or as currently look for the word "header" and change the line e.g. cells B17-O17 to the white text on black background.

My VBA skills are basic and I can manipulate them but struggle to write them.

Any and all help is appreciated.

Charlotte.

Answer 1

Hi:

Try this small macro:

Sub Unconditional()

Dim N As Long, L As Long, r As Range

N = Cells(Rows.Count, "A").End(xlUp).Row

For L = 1 To N

    If Cells(L, "A").Value = "Header" Then

        Set r = Range("A" & L & ":O" & L)

        With r

        .ClearFormats

        .Font.ColorIndex = 2

        .Interior.ColorIndex = 1

        End With

End If

Next

End Sub

Answer 2

You'll lose all other font and fill color in columns A:O.

Sub SetHeaderFormatting()

    Dim rng As Range

    Dim s As String

    Application.ScreenUpdating = False

    ' Clear previous formatting

    With Range("A:O")

        .Font.ColorIndex = xlColorIndexAutomatic

        .Interior.ColorIndex = xlColorIndexNone

    End With

    ' Find all instances of "Header" in column A

    With Range("A:A")

        Set rng = .Find(What:="Header", LookIn:=xlValues, LookAt:=xlWhole)

        If Not rng Is Nothing Then

            s = rng.Address

            Do

                ' Invert color

                With rng.Resize(ColumnSize:=15)

                    .Font.Color = vbWhite

                    .Interior.Color = vbBlack

                End With

                Set rng = .FindNext(After:=rng)

            Loop Until rng.Address = s

        End If

    End With

    Application.ScreenUpdating = True

End Sub