Conditional formatting across worksheets in the same workbook.

[BombCenter]

Hello world!

I'm trying to (programmatically on createSheet_click) conditionally format a cell's color if it does not equal the value of a cell on another worksheet.

Basically, something like this:
If ('Std. Kitchen with Granite'!L9 != 'Std. Kitchen'!L9) Then [Highlight the cell]
I know the syntax is off, I'm not familiar with VBA syntax specifically, though you get the gist.

I've attempted to do this the easy way by creating conditional formatting rules, but my copy of Excel 2007 (SP2) says it cannot reference values on another worksheet. However, John Walkenbach's Excel Bible 2007 says its possible (bottom of page 423) and MSDN (http://msdn.microsoft.com/en-us/library/bb286672(v=office.11).aspx) also concurs.

Individual named ranges are an impossibility since I'll be highlighting changes on 8+ columns across 40+ worksheets.

Is there some special procedure I have to follow to do this? Or are both MSDN and Walkenbach in error?

 

[VoG]

This can be easily done using VBA. Can you provide a concrete example?

 

[BombCenter]

Function newSheet(source As String)
    newSheetName = InputBox("Please enter a name for the new Tab:", Toolbox.Caption)
    If newSheetName = "" Then Exit Function
    ActiveWorkbook.Sheets(source).Visible = True
    ActiveWorkbook.Sheets(source).Copy After:=Worksheets(source)
    ActiveWindow.ActiveSheet.Name = newSheetName
    If source = "Template" Then ActiveWorkbook.Sheets(source).Visible = False
    
    ' Sets up named ranges:
    If source = "Template" Then
        ActiveSheet.Range("L170").Name = "'" & newSheetName & "'!Contingency"
        ActiveSheet.Range("O173:AR173").Name = "'" & newSheetName & "'!Sell"
        ActiveSheet.Range("AR175").Name = "'" & newSheetName & "'!Total"
        ActiveSheet.Range("AS175").Name = "'" & newSheetName & "'!SellTotal"
    End If

' Applies conditional formatting to specified ranges:
' #####################
' # Conditional formatting code #
' #####################
    
    ' Prompts for entry creation on copied sheets:
    If source <> "Template" Then
        Response = MsgBox("Would you like to create entries on the 'Master Summary' and 'Overview' Tabs for '" & _
            newSheetName & "'?", vbQuestion + vbYesNo, Toolbox.Caption)
        If Response = vbNo Then
            ' Reactivates New Sheet:
            Call sheetListPopulate
            ActiveWorkbook.Worksheets(newSheetName).Activate
            If IsNull(yOffset) Or yOffset = 0 Or IsNull(Placement(3)) Or Placement(3) = 0 Then Exit Function
            ActiveSheet.Cells(yOffset, Placement(3)).Select
            Exit Function
        End If
    End If
.
.
.
End Function

Essentially this code snippet gets passed source as String, which comes off the sheetList listbox on form Toolbox or ToolboxSmall. If a specific sheet is selected off the list, it copies that sheet, otherwise it copies a hidden, blank master template. When a specific sheet is selected, I'd like a conditional format to be applied to the copy on specified column numbers (placement(5) and placement(21) or columns 14 and 12, respectively) that is the technical equivalent of:

for i = yMasterOffset to SheetLength (circa row 161, but will change if rows are inserted)
If oldSheetName.Range.Cells(i, xMasterOffset + placement(6, 12)).Value <> newSheetName.Range.Cells(...).Value Then [apply some conditional formatting programmatically]
next i

I want the formatting to be retroactive and act in real time, not just on copySheet_Click. I'd like to avoid copying the specified columns from the source worksheet to use as a mirror, as the source will be changing frequently.

you want a full attached copy of the workbook? its rather large, and will error if the database is not found.

 

[BombCenter]

Nevermind I got it. Thanks.