Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Apply a rule to all worksheets

    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
    Last edited by kerryg; 2015-06-11 at 00:04.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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"

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files
    Last edited by zeddy; 2015-06-11 at 10:17. Reason: updated code to deal with multiple pasted cells

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Last edited by zeddy; 2015-06-11 at 13:33.

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Zeddy,

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

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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

  11. #11
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    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

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Sorry I misunderstood. I thought you wanted to apply the conditional formatting to the entire workbook.

    Maud

  13. #13
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Maud I do want to apply it to the whole workbook, however on each worksheet it needs to be columns A to AF.

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    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
    Attached Files Attached Files
    Last edited by zeddy; 2015-06-12 at 05:30.

  15. #15
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Hi Zeddy

    Sorry but this doesnt work?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •