Conditional Formatting with XML - how to select a subrange

[Colleyville Alan]

I recently became aware of the possibility of using conditional formatting
to highlight the cells in a spreadsheet which contain formulas. This uses
the GET.CELL method from XML. While it cannot reference another workbook
(like Personal.xls), I have still been able to write a macro in my
Personal.xls folder that sets the name range and conditional references in
the active workbook; while I "wrote" it, I heavily borrowed from others
posts on a website The function and sub below toggle this conditional
formatting for the entire worksheet.

Question: how do I set this up so that only a selected range has the
formatting change? I do not understand the Get.Cell command or syntax and
have yet to find an explanation of it. I do not actually know what it is
doing other than it does make the cells with formulas change their fill
color. Can someone explain how to change only a selected range and how the
Get.Cell command works (or a link)?
Thanks much.



Sub DisplayFormulas()
ScreenUpdating = False
If NameExists("DisplayFormula") = False Then
ActiveWorkbook.Names.Add Name:="DisplayFormula", RefersToR1C1:= _
"=GET.CELL(48,INDIRECT(""rc"",FALSE))"
Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=DisplayFormula"
Selection.FormatConditions(1).Interior.ColorIndex = 34
Range("A1").Select
Else
ActiveWorkbook.Names("DisplayFormula").Delete
End If
Range("A1").Select
ScreenUpdating = True
End Sub

Function NameExists(TheName As String) As Boolean
On Error Resume Next
NameExists = Len(ActiveWorkbook.Names(TheName).Name) <> 0
NameExists = Len(ActiveWorkbook.Names("DisplayFormula").Name) <> 0
End Function

 

[Dave Peterson]

Too many acronyms. I bet you meant XLM (ye olde style macro language for excel)
not XML (extensible markup language).

But I dumped a little of the code.

Option Explicit
Sub DisplayFormulas()
Application.ScreenUpdating = False

ActiveWorkbook.Names.Add Name:="DisplayFormula", RefersToR1C1:= _
"=GET.CELL(48,INDIRECT(""rc"",FALSE))"
With ActiveSheet.Range("a3:b99")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=DisplayFormula"
.FormatConditions(1).Interior.ColorIndex = 34
End With

Application.ScreenUpdating = True
End Sub

You can write over the name by just reassigning it. So why check.

And you can specify your range with this statement:

With ActiveSheet.Range("a3:b99")

 

[Colleyville Alan]

Too many acronyms. I bet you meant XLM (ye olde style macro language for excel)
not XML (extensible markup language).

Yeah, XLM it is.

But I dumped a little of the code.

Option Explicit
Sub DisplayFormulas()
Application.ScreenUpdating = False

ActiveWorkbook.Names.Add Name:="DisplayFormula", RefersToR1C1:= _
"=GET.CELL(48,INDIRECT(""rc"",FALSE))"
With ActiveSheet.Range("a3:b99")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=DisplayFormula"
.FormatConditions(1).Interior.ColorIndex = 34
End With

Application.ScreenUpdating = True
End Sub

You can write over the name by just reassigning it. So why check.

I wanted the macro to toggle the formatting on and off. It overrides all
other formatting, so having it on all of the time is not optimal. Likewise,
it would be nice to have different formatting in different parts of the
worksheet, hence my request for info on using only a selected range.

And you can specify your range with this statement:

With ActiveSheet.Range("a3:b99")

Thanks. This works if I use it by itself when the workbook is first opened.
But if I had turned on the conditional formatting for the entire sheet and
then turned it off, this macro of yours no longer works for just the range,
it now selects the entire worksheet. Apparently, the code I am using to
turn the formatting off, while it seems to work, is not doing what I had
thought it was doing. Another mystery to be solved.

Thanks again.

 

[Dave Peterson]

I don't think your original macro actually turned off the conditional
formatting. You deleted the name so the formula inside the conditional
formatting evaluated to an error (or False) so the color disappeared.

You could add that toggle back in by checking the existence of the range.

But you may just want to use part of your code:

..FormatConditions.Delete

to remove any conditional formatting.

 

[Colleyville Alan]

I don't think your original macro actually turned off the conditional
formatting. You deleted the name so the formula inside the conditional
formatting evaluated to an error (or False) so the color disappeared.

You could add that toggle back in by checking the existence of the range.

But you may just want to use part of your code:

.FormatConditions.Delete

to remove any conditional formatting.

Gotcha. works great - thanks