PDA

View Full Version : How do I fix this VBA Code?



keatenclarno
07-17-2018, 10:13 AM
Dim i As Integer
i = 20
Do While i <= 2215
Range("Z3:Z19").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="='Look up Tables'!Zi"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
i = i + 1
Loop


I know it's wrong, but I don't understand how something like this wouldn't work. Also, how would I get it to work and still do the same thing?

Paul_Hossler
07-17-2018, 11:42 AM
1. I added CODE tags to your macro -- you can use the [#] incon next time to add them

2. No need to .Select or .Scroll - you can work directly on objects

3. This part 'looks wrong'




Formula1:="='Look up Tables'!Zi"




If I always = 20 then try




Formula1:="='Look up Tables'!Z20"


If not, then try



Formula1:="='Look up Tables'!Z" & i



Otherwise attach a workbook with the WHOLE macro and details