Consulting

Results 1 to 4 of 4

Thread: Conditional formating using relative references

  1. #1
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location

    Conditional formating using relative references

    Hi,
    I have a code that works with an absolute formula to conditionally format cells that are zero in row 11 and have a value in row 10 but as these rows will continue to change I now need to use relative references and can't get it right. Please see attached. I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)
    test.xlsm
    Regards, Peter.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,074
    try:
    Sub Relative2()
    With Selection.Resize(, 20)
      LowerCellAddress = .Cells(1).Address(False, False)
      UpperCellAddress = .Cells(1).Offset(-1).Address(False, False)
      .FormatConditions.Delete
      With .FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(" & UpperCellAddress & ">0," & LowerCellAddress & "=0)")
        .SetFirstPriority
        With .Interior
          .PatternColorIndex = xlAutomatic
          .Color = 65535
          .TintAndShade = 0
        End With
        .StopIfTrue = False
      End With
    End With
    End Sub
    Since posting, it's easier than that; change your:
    Selection.FormatConditions.Add Type:=xlExpression, FormulaR1C1:="=AND(R[-1]C[0]>0,R[0]C[0]=0)"
    to:
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(R[-1]C[0]>0,R[0]C[0]=0)"

    so simplified a bit:
    Sub Relative3()
    With Selection.Resize(, 20).FormatConditions
      .Delete
      With .Add(Type:=xlExpression, Formula1:="=AND(R[-1]C[0]>0,R[0]C[0]=0)")
        .SetFirstPriority
        With .Interior
          .PatternColorIndex = xlAutomatic
          .Color = 65535
          .TintAndShade = 0
        End With
        .StopIfTrue = False
      End With
    End With
    End Sub
    Last edited by p45cal; 04-24-2014 at 04:15 PM.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location
    HI p45cal,
    Brilliant, that works perfectly. Thank you very much for your time and expertise.

    Yet again VBA express has proved to be the place to get answers and get them quickly.
    Thank you.
    Regards, Peter.

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,970
    In E11:

    conditional format formula "=And(E$10>0,E$11=0)"
    applies to $E$11:$X$11

Posting Permissions

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