VBA - Using defined name 'Me' in conditional formatting

Conditional formatting can be powerful. Here is a tip which helps writing conditional formatting functions. Often you want some format to be determined by the current cell rather than some other cell. In these cases it helps to define a named range called 'Me' which is defined in relative terms (i.e. using RC syntax) as just RC (theoretically R[0]C[0]). Then you can write formulas based on Me.

So the code below defines the name Me and then uses it to color in any cell than is non-empty, the formula is

=(LEN(Me)>0)

VBA source code

1. Option Explicit
2.  
3. Function TryItem(ByVal col As Object, ByVal sItem As String, ByRef pobjReturn As Object) As Boolean
4.     On Error Resume Next
5.     Set pobjReturn = col.Item(sItem)
6.     TryItem = Not (pobjReturn Is Nothing)
7.  
8. End Function
9.  
10. Sub UsingMeInConditionalFormatting()
11.  
12.     Dim wb As Excel.Workbook
13.     Set wb = ThisWorkbook
14.  
15.     Const sWorksheet As String = "Sheet1"
16.  
17.     Dim ws As Excel.Worksheet
18.     Set ws = wb.Worksheets(sWorksheet)
19.  
20.     Dim objNameMe As Object
21.     Dim namMe2 As Excel.Name
22.     If Not TryItem(ws.Names, "Me", objNameMe) Then
23.         'Stop
24.         Set namMe2 = ws.Names.Add(Name:="Me", RefersToR1C1:="='" & sWorksheet & "'!RC")
25.     Else
26.         Set namMe2 = objNameMe
27.         Set objNameMe = Nothing
28.     End If
29.     'Stop
30.  
31.     Dim rngUsedRangeTopTenRows As Excel.Range
32.     Set rngUsedRangeTopTenRows = ws.UsedRange.Rows("1:10")
33.  
34.     rngUsedRangeTopTenRows.FormatConditions.Delete
35.  
36.     Dim formatCond As FormatCondition
37.  
38.     Set formatCond = rngUsedRangeTopTenRows.FormatConditions.Add(Type:=xlExpression, Formula1:= _
39.         "=(Len(Me)>0)")
40.  
41.     With formatCond.Interior
42.         .PatternColorIndex = xlAutomatic
43.         .Color = VBA.RGB(132, 190, 0)  'grass green
44.         .TintAndShade = 0
45.     End With
46.  
47. End Sub