Hi again people,

Is it possible to put a loop on conditional formatting

for example

im using an integer lngRow in a loop but i cant get this to work. Does anybody know the correct syntax for this.

election.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($AD$&lngRow=""Yes"", $AE$&lngRow=""no"")"

any ideas guys?

Could you explain the conditional formatting and the need for a loop?

You can set conditional formatting for a range and Excel will automatically change the references as required.

Also can we see the rest of the code?

Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = Range("Y65356").End(xlUp).Row

For lngRow = 2 To lngLastRow


If Range("Y" & lngRow).Value <> "" Then Range("Z" & lngRow).Activate



Windows("rrvtest2.xls").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND ($AD$&lngRow =""Yes"", $AE$&lngRow=""no"")" I have changed all the &lngRow originally it was "2"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($AD$&lngRow=""Yes"", $AE$&lngRow=""no"")"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$AE$&lngRow=""Yes"""
Selection.FormatConditions(2).Interior.ColorIndex = 4
Windows("rrvtest2.xls").Activate
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND (Cells($AD$,lngRow)=""Yes"", Cells($AE$,lngRow)=""no"")"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$AE$&lngRow=""Yes"""
Selection.FormatConditions(2).Interior.ColorIndex = 4
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("rrvtest2.xls").Activate
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A11"), Type:= _
xlFillDefault
ActiveCell.Range("A11").Select
ActiveCell.Select
ActiveCell.Offset(0, -1).Range("A1").Select
Windows("rrvtest2.xls").Activate
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Civil"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Attachments"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:B1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:B1").Select
ActiveCell.Offset(0, 3).Range("A1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($AD$&lngRow=""Yes"", $AE$&lngRow=""no"")"
Selection.FormatConditions(1).Interior.ColorIndex = 6
ActiveCell.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($AD$&lngRow=""Yes"", $AE$&lngRow=""no"")"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$AE$&lngRow=""Yes"""
Selection.FormatConditions(2).Interior.ColorIndex = 4
ActiveCell.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Yes"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Yes"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AQ$&lngRow=Yes"
Selection.FormatConditions(1).Interior.ColorIndex = 4
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:L1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:L1").Select
ActiveCell.Select
ActiveCell.FormulaR1C1 = ""
ActiveCell.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IE"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Offset(0, 1).Range("A1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Labour"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Yes"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


'Range("Z" & lngRow).Value = Range("Z" & lngRow).Value + 1



Next lngRow


End Sub

Im trying to run a macro on differnt lines hence why i need the formatting to be a variable.

Any ideas?

Well one thing I can tell you, there is absolutely no need for all that activating/selecting etc.

As to how to incorporate the variable, try this.
I also think you could avoid using the loop but I'm not 100% sure what you are actually trying to do with the code.

Hi Norie,

Thankyou for taking the time to reply to me.

Well what i have done is created a macro to work on a specific cell (next to a cell that has data ie. if Y3 has data i will run macro in Z3) since the macro is relative i can run it on different rows.

Im trying to use a loop to detect the number of cells that have data in them then run the macro on the cells next to it. ie if Y6 is blank Z6 wont be the active cell. if Y7 has data it will make Z7 the active cell to run the macro.

I have tried the operand on either sides and the speech marks but the coded data becomes red.

any ideas?

Did you try what I posted?

That compiled fine for me.

By the way I realise that you only want to do this when column Y has data.

It's what you are actually trying to do I can't quite work out.

yes thats what i want to do. Is it possible to post a screen shot here?

I have this hilighted

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND ($AD$" & lngRow & " =""Yes"", $AE$" & lngRow & "=""No"")"
and a msg appears 'invalid procedure call or argument'

any ideas Norie? anyone?

Hi

Try changing this

"=AND ($AD$" & lngRow & " =""Yes"", $AE$" & lngRow & "=""No"")"

to this

"=AND($AD$" & lngRow & " =""Yes"", $AE$" & lngRow & "=""No"")"

ie remove the space in this 'AND ('

This seems to produce the error you are getting.

HTH

MTB

mike... ima get u a new bike... ur a genius.. thankyou

wow, one space causing so much trouble, oh well...thats why i always use vba coding to do almost everything in excel, formula just so hard to debug. Don't you guys agree?

Eh, isn't the OP using VBA?