Applying Conditional Formatting to same cells for multiple sheets (170)

[SteveT24]

Greetings Mr.Excel community,

First time user as I just discovered the forum, but I can already see myself bringing complex Excel questions here instead the often-fruitless Google searches I've relied on up to this point.

Excel File: Project Estimate Workbook
Purpose: 170 Bid Sheets exist for detailed bid/pricing information that allows for side by side comparison of participating bidders. Functions exist to compare the bid sum & determine the low bidder. A summary sheet then calls the low bidder's name & bid total for each sheet to provide a project total.

My Question: I've recently implemented conditional formatting to highlight specific areas within each bid sheet for emphasis when competing bids are compared. Is there any way for me to create Conditional formatting to apply to specific Cells ($C$1-$G$1) all of the bid sheets ('1'-'170')? I've been using the format painter to apply to sheets individually, but it's time consuming to comb thru 170 sheets.

Any insight or suggestions to make this process more efficient would be greatly appreciated. Thanks all.

 

[Joe4]

Welcome to the Board!

You can you use VBA to do this. You don't actually really need to know much VBA to do this. Simply turn on your Macro Recorder, and record yourself performing the Conditional Formatting you need to one of the sheets. Then stop the Macro Recorder and inspect your code. This will give you most of the code that you need.

If you see any specific sheet references in the code, change them to "ActiveSheet". And then take the body of that code, and paste into this "shell" of VBA code (the part in red), which will loop through all the sheets in your workbook.

Sub Macro1()

    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.Name <> "Total" Then
            ws.Activate
[COLOR=#ff0000]            'paste conditional formatting code here[/COLOR]
            MsgBox ws.Name
        End If
    Next ws
    
End Sub

Note that if you have any sheets that you want to exclude from this Conditional Formatting, you can exclude then by this row here:

        If ws.Name <> "Total" Then

In this example, I am excluding a sheet named "Total". If you have a few that you need to exclude, you would do that like this:

        If (ws.Name <> "Sheet1") And (ws.Name <> "Sheet2") And (ws.Name <> "Sheet3") Then

If you have more than a handful, there are other more efficient ways we can use to exclude them. I would just need to know the name structure of the sheets you want to apply it to versus the name structure of the ones you don't.