Conditional formatting based on whether a specific sheet name is present

[SquareJerBear]

Hello,

I have a series of specific templates that are being added to a workbook.

I would like to highlight a cell in the first sheet of the workbook if the workbook currently has a worksheet by that name present.

For example, if I add a template named "Saturday" to the worksheet, I would like a cell on the first sheet to highlight to indicate that this sheet is present.

Any ideas?

 

[mumps]

I'm not sure if you can do that with conditional formatting. But his macro should do it. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for the sheet containing the cell you want highlighted and click 'View Code'. Paste the macro into the empty code window that opens up. Change the range (in red) to match the cell in question. Close the code window to return to your sheet. The macro is triggered automatically when you activate the sheet.

Private Sub Worksheet_Activate()
    If Evaluate("isref('" & Range("A1").Value & "'!A1)") Then
        Range("A1").Interior.ColorIndex = 3
    Else
        Range("A1").Interior.ColorIndex = xlNone
    End If
End Sub

 

[Fluff]

Assuming your sheet names in col A, select A1 to end of data in col A & use this formula in conditional formatting
=ISREF(INDIRECT("'"&A1&"'!A1"))

Change the red A1 to reference the first cell in the applies to range