VBA ActiveCell location to start Conditional Formatting

[jjlafond]

Hello,

I am trying to apply Conditional formatting using VBA from an active cell location. I can't get it working no matter what I do.

I think I've narrowed down the problem to the bolded parts. If I have an actual cell address (C19) I can get it to work for a range starting with C19.

I need to be able to run the Conditional Format as part of a Sub. I have ActiveCell locations but the operator at the screen does not know the location to start.

This is what I have at the moment:


startCell = ActiveCell.Address(0, 0)
MsgBox startCell


ActiveCell.Resize(6, NumCavLines).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(LEN(TRIM(startCell))>0,OR(startCell>$L$6,startCell<$M$6))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[NeonRedSharpie]

Not that you need a new thread for this question...but:

[COLOR=#333333]startCell = ActiveCell.Address(0, 0)[/COLOR]

Doesn't exist. The syntax is (ROW, COLUMN) which is the formula I gave you in the other post you made. Remove the (0,0) and try again. That will always return NULL.

 

[jjlafond]

It is a more specific problem, so I started a new thread for it.

startCell = ActiveCell.Address(0, 0)
MsgBox startCell
Does work and returns C19 as the value in the MsgBox.

But it doesn't in Conditional Formatting either. You gave me code for cells before, and it didn't work. I thought that since the recorded macro had range values (C19), maybe startCell value as a range would work. It also failed, no errors, but no formatting either.