Results 1 to 13 of 13
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: Variable conditional formatting

    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?

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    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?

  3. #3
    Join Date
    May 2006
    Posts
    178
    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?

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    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.
    Code:
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=AND ($AD$" & lngRow & "=""Yes"", $AE$" & lngRow &  "=""no"")"
    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.

  5. #5
    Join Date
    May 2006
    Posts
    178
    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?

  6. #6
    Join Date
    Mar 2006
    Posts
    163
    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.

  7. #7
    Join Date
    May 2006
    Posts
    178
    yes thats what i want to do. Is it possible to post a screen shot here?

  8. #8
    Join Date
    May 2006
    Posts
    178
    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'

  9. #9
    Join Date
    May 2006
    Posts
    178
    any ideas Norie? anyone?
    Last edited by aboo; 05-31-06 at 16:40.

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  11. #11
    Join Date
    May 2006
    Posts
    178
    mike... ima get u a new bike... ur a genius.. thankyou

  12. #12
    Join Date
    Jun 2006
    Posts
    103
    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?

  13. #13
    Join Date
    Mar 2006
    Posts
    163
    Eh, isn't the OP using VBA?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •