tinak
09-03-2014, 06:15 AM
Hi
I will start with apology as struggling to explain what I need and just hope somebody understands what i am after
I need to change mydate4 to activecell column row 1 instead of a range name
eg if in column H mydate4 would equal H1
I need to do this as looping through a number of columns and cannot relocate mydate4 as affects conditional format of all loops
Do While ActiveCell.offset(-3,0) > 0
ActiveCell.Name = "mystart"
ActiveCell.Offset(-3, 0).Name = "mydate4"
ActiveCell.Offset(mynumber, 0).Name = "myend"
Range("mystart:myend").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$E4=mydate4"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
activecell.offset(0,2).select
loop
thank you
Tina
I will start with apology as struggling to explain what I need and just hope somebody understands what i am after
I need to change mydate4 to activecell column row 1 instead of a range name
eg if in column H mydate4 would equal H1
I need to do this as looping through a number of columns and cannot relocate mydate4 as affects conditional format of all loops
Do While ActiveCell.offset(-3,0) > 0
ActiveCell.Name = "mystart"
ActiveCell.Offset(-3, 0).Name = "mydate4"
ActiveCell.Offset(mynumber, 0).Name = "myend"
Range("mystart:myend").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$E4=mydate4"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
activecell.offset(0,2).select
loop
thank you
Tina