asked on

vba conditional formating?

I have code that fill some cells on destiantion workbook and add conditional formating on them
when i test my code first time on first entry it works great but when i continue working and continue repeating same process it input formating on totally different cells

Code that does filling of those specific cells i need and add conditional formating on them is attached and you can clearly see that it should put formating on range k5:k500 and on l5:l500
but it does that only first time i use it all next tries end up with it setting formating on j column instead of k or on m column instead of L only few of them is there as it should be
if anyone can help me here since i don't know what else to try.as much as i can see this should work perfect but it doesn't

'insert formulas
Sheets(vrsta).Range("e5").AutoFill Destination:=Sheets(vrsta).Range("e5:e6"), Type:=xlFillDefault
Sheets(vrsta).Range("f5").AutoFill Destination:=Sheets(vrsta).Range("f5:f6"), Type:=xlFillDefault
Sheets(vrsta).Range("g5").AutoFill Destination:=Sheets(vrsta).Range("g5:g6"), Type:=xlFillDefault
Sheets(vrsta).Range("i5").AutoFill Destination:=Sheets(vrsta).Range("i5:i6"), Type:=xlFillDefault
Sheets(vrsta).Range("k5").AutoFill Destination:=Sheets(vrsta).Range("k5:k6"), Type:=xlFillDefault
Sheets(vrsta).Range("l5").AutoFill Destination:=Sheets(vrsta).Range("l5:l6"), Type:=xlFillDefault
'conditional formating za crvenu boju ako je otisao u minus
Sheets(vrsta).Range("K5:K500").FormatConditions.Delete
    Sheets(vrsta).Range("K5:K500").FormatConditions.Add Type:=xlExpression, Formula1:="=K5<0"
    Sheets(vrsta).Range("K5:K500").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Sheets(vrsta).Range("K5:K500").FormatConditions(1).Interior.ColorIndex = 3
    Sheets(vrsta).Range("K5:K500").FormatConditions(1).Interior.TintAndShade = 0
    Sheets(vrsta).Range("L5:L500").FormatConditions.Delete
    Sheets(vrsta).Range("L5:L500").FormatConditions.Add Type:=xlExpression, Formula1:="=L5<0"
    Sheets(vrsta).Range("L5:L500").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Sheets(vrsta).Range("L5:L500").FormatConditions(1).Interior.ColorIndex = 3
    Sheets(vrsta).Range("L5:L500").FormatConditions(1).Interior.TintAndShade = 0
End Sub

Open in new window

Rory Archibald

Try this:

'insert formulas
   With Sheets(vrsta)
      .Select
      .Range("e5").AutoFill Destination:=.Range("e5:e6"), Type:=xlFillDefault
      .Range("f5").AutoFill Destination:=.Range("f5:f6"), Type:=xlFillDefault
      .Range("g5").AutoFill Destination:=.Range("g5:g6"), Type:=xlFillDefault
      .Range("i5").AutoFill Destination:=.Range("i5:i6"), Type:=xlFillDefault
      .Range("k5").AutoFill Destination:=.Range("k5:k6"), Type:=xlFillDefault
      .Range("l5").AutoFill Destination:=.Range("l5:l6"), Type:=xlFillDefault
      'conditional formating za crvenu boju ako je otisao u minus
      .Range("K5").Select
      .Range("K5:K500").FormatConditions.Delete
      .Range("K5:K500").FormatConditions.Add Type:=xlExpression, Formula1:="=$K5<0"
      .Range("K5:K500").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
      .Range("K5:K500").FormatConditions(1).Interior.ColorIndex = 3
      .Range("K5:K500").FormatConditions(1).Interior.TintAndShade = 0
      .Range("L5:L500").FormatConditions.Delete
      .Range("L5:L500").FormatConditions.Add Type:=xlExpression, Formula1:="=$L5<0"
      .Range("L5:L500").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
      .Range("L5:L500").FormatConditions(1).Interior.ColorIndex = 3
      .Range("L5:L500").FormatConditions(1).Interior.TintAndShade = 0
   End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Patrick Matthews

Rats, slow fingers