Execute Conditional Formatting Code on TWO events

[JustinDevine]

DRJ was kind enough to give me the following code to place in worksheet_Change. It works wonderfully. all I wanted was to acheive the same function but not only during a cell change but also on workbook_open. Oh and prefereably on every sheet not just the sheet the code is attached to. Sorry I am new to this and my previous posts have been poorly described. I don't FULLY understand the code below as I don't know VBA, but have programmed before so I understand the case statements but not all the declarations etc... my goal is basically to perform conditional formatting on multiple events. I'd like to use only one set of code if possible and call it on multiple events. Please help, I have been trying to figure this out for a week+ and my VBA book hasn't come from Amazon.com :-(

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Tom", "Paul", "John"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "Smith", "Jones"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case 1, 3, 7, 9
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 10 To 25
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case 26 To 99
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

[austenr]

Hi,

I am a little confused. If you have the code working with the code posted above, why would you want to do it on WorkbookOpen since your cells would be formatted already and simply opening the workbook would not change any cell contents. :dunno

[JustinDevine]

Well the issue is I know the CASE statements WILL change at some point. The issue with this code is that when they change nothing changes the formatting of the already formatted cells.. so they stay colored, even if you take away the criteria that made them change color. I guess I would be happy with a "reevaluate everything" snipet of code as well. Can you help?