Apply a rule to all worksheets

Topic

New Reply

Hello again

I have a workbook which contains many worksheets. Each worksheet has a series of questions to be answered with the placement of a X in a cell. I have made a rule using conditional formatting – where there is an X, the cell is highlighted light green. This gives a quick visual of questions that have been answered. I can do this easily on each individual worksheet by applying the rule to the whole sheet. My problem is that I have a multitude of them and I would like to know how I can apply this rule to the complete workbook. Could this be done with VB?

Any assistance will be most appreciated.

Kerry

Reply | Quote

Replies

Kerryg,

This macro will apply the formatting to the entire workbook. Be careful placing this in the workbook_Open event routine or it will add a new rule each time you open the book. You could use conditional testing prior to applying the rule if you prefer.

In a standard module:

Code:

Sub FormatRange()
For I = 1 To Worksheets.Count
    Worksheets(I).Cells.FormatConditions.Add 1, xlEqual, “=””X”””
    Worksheets(I).Cells.FormatConditions(1).Interior.Color = vbGreen
Next I
End Sub

HTH,
Maud

Reply | Quote

Hello Maud
Thanks for your response. I am able to place this code in a standard module, but I dont understand how to
“place this in the workbook_Open event routine”

Reply | Quote

Hi Kerry

Another way is to use the Workbook SheetChange event:

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

For Each cell In Target                 'process each cell in changed range
zCol = cell.Column                      'get column number for changed cell

Select Case zCol                        'take action depending on column
Case [j1].Column, [k1].Column           'change detected in col [J] or [K]
If UCase(cell.Value) = "X" Then         'test if cell value is "x" or "X"
cell.Interior.Color = rgbPaleGreen      'change cell colour
Else                                    'otherwise..
cell.Interior.Color = xlNone            'clear cell colour
End If                                  'end of test for "x" or "X"

Case Else                               'for all other columns..
'do nothing
End Select

Next cell                               'process next cell in changed range

End Sub

Copy this routine, and then paste it into the code window for ThisWorkbook in the VBE.

This routine will change the background cell colour to pale green whenever you enter an “x” in columns [J] or [K] on any sheet. You can easily adapt this to suit.
see attached file

zeddy

Reply | Quote

Hi Kerryg,

You could place it in the workbook_Open event routine but it would duplicate the rule everytime you opened the book unless you had some conditional testing to see if a conditional formula already exists. It is done through the ThisWorkbook module and would look like this.

Code:

Private Sub Workbook_Open()
For I = 1 To Worksheets.Count
    Worksheets(I).Cells.FormatConditions.Add 1, xlEqual, “=””X”””
    Worksheets(I).Cells.FormatConditions(1).Interior.Color = vbGreen
End Sub

If you will be running this on occasion such as whenever you add a new sheet, then placing it in a standard module is just fine.

You could also use Zeddy’s method which does not use conditional formatting but rather checks each input for an X

Reply | Quote

Hi Maud

..your code is much shorter!
(..but I still like rgbPaleGreen rather than vbGreen)

(..don’t forget the required Next in a For .. Next loop)

zeddy

Reply | Quote

Hi Kerry

Maud’s method is neat and effective.

As suggested by Maud, I have added a test when the workbook is opened, to prevent the conditional formatting being repeated over and over again each time the workbook is loaded.
In my example attached, I used cell [Z1] to store a value (“Done”) when the conditional format is first added for a sheet.
You could easily change this to use any ‘unused’ cell, and even ‘hide’ it if required.

Code:

Private Sub Workbook_Open()
For I = 1 To Worksheets.Count
    If Worksheets(I).[z1]  "Done" Then
    Worksheets(I).Cells.FormatConditions.Add 1, xlEqual, "=""X"""
    Worksheets(I).Cells.FormatConditions(1).Interior.Color = vbGreen
    Worksheets(I).[z1] = "Done"
    End If
Next
End Sub

zeddy

Reply | Quote

Zeddy,

Very clever and much simpler than the concept I was thinking of with the counting existing number of format conditions

Reply | Quote

Hi Maud

..I have now learnt from you that it’s OK to apply a conditional format to over 1 million rows with impunity!
..your applying it to the entire sheet was a stroke of genius.
..probably couldn’t do that in Excel2003!

zeddy

Reply | Quote

Zeddy,

Thanks for the pep! As far as counting the conditions, my concept was to take cell A1 and count the format conditions applied to that cell. Assuming that the condition is the same for all the cells (assigned by code) if cell A1 has a condition then inhibit adding a second. If cell A1 does not have a format condition, then go ahead and apply it.

I still think your scheme on how to set up the conditional testing is less cumbersome.

Maud

Reply | Quote

Hi Zeddy and Maud

You fellows are so darn clever! I have had a play and I think I prefer the method that doesn’t use conditional formatting but I want to apply it to “A1:AF348” but done know how to apply this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

For Each cell In Target ‘process each cell in changed range
zCol = cell.Column ‘get column number for changed cell

Select Case zCol ‘take action depending on column
Case [j1].Column, [k1].Column ‘change detected in col [J] or [K]
If UCase(cell.Value) = “X” Then ‘test if cell value is “x” or “X”
cell.Interior.Color = rgbPaleGreen ‘change cell colour
Else ‘otherwise..
cell.Interior.Color = xlNone ‘clear cell colour
End If ‘end of test for “x” or “X”

Case Else ‘for all other columns..
‘do nothing
End Select

Next cell ‘process next cell in changed range

End Sub

Reply | Quote

Sorry I misunderstood. I thought you wanted to apply the conditional formatting to the entire workbook.

Maud

Reply | Quote

Maud I do want to apply it to the whole workbook, however on each worksheet it needs to be columns A to AF.

Reply | Quote

Hi Kerry

this should do it..

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Column > [AF1].Column Then Exit Sub
If Target.Row > 348 Then Exit Sub

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each cell In Target                 'process each cell in changed range
If UCase(cell.Value) = "X" Then         'test if cell value is "x" or "X"
cell.Interior.Color = rgbPaleGreen      'change cell colour
Else                                    'otherwise..
cell.Interior.Color = xlNone            'clear cell colour
End If                                  'end of test for "x" or "X"

Next cell                               'process next cell in changed range
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

..see attached workbook.
You can change the max row (348) and column (AF) easily as required.

zeddy

Reply | Quote

Hi Zeddy

Sorry but this doesnt work?

Reply | Quote

Hi Kerry

..the code as posted in post#14 works very well.

..it’s just that in the posted file, I accidentally left an ‘Exit Sub’ in the first line of code in that workbook.
(I was doing some cleanup tasks, and forgot to remove that Exit Sub when I saved that v1b file)
I have taken that line out in this attached version!

zeddy

Reply | Quote

Kerryg,

Going back to conditional formatting, this will work:

Code:

Sub FormatRange()
For I = 1 To Worksheets.Count
    Worksheets(I).Range(“A1:AF348”).FormatConditions.Add 1, xlEqual, “=””X”””
    Worksheets(I).Range(“A1:AF348”).FormatConditions(1).Interior.Color = vbGreen
Next I
End Sub

HTH,
Maud

Reply | Quote

Hello Maud

Thank you so much for your response. This works perfectly.

Reply | Quote