I create report worksheets. While most data entry is into single cells there are some that are merged cells.
I have been manually setting the cell conditional format to be say a yellow back colour if the cell is blank. When data is entered the cell reverts to its native colour.
I can copy the (conditional) format from single cell to single cell, but it doesn't work to merged cells (breaks them up obviously).
There is also different number formats among the single cell data entry cells and they end up inheriting all the format attributes from the copied cell.

To save the repetitious conditional formatting is there a way I can script this in VBA. I could then assign this to say a button on the Quick Access Toolbar or a hot key.
To say, for the selected cell in a worksheet conditionally format is so that when blank it has a yellow back colour.

When I record a macro to do this I get:

Sub CondFormatCell()
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(W14))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

End Sub

where W14 was the active cell.
I understand the "=LEN(TRIM(W14))=0" means if the cell is blank, but I need this reference to be for any active cell or merged cells I choose on the worksheet and the conditional formatting to apply to the same cell(s). Hope that's clear.
Any help appreciated

Piri