Consulting

Results 1 to 4 of 4

Thread: Conditional formating using relative references

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,970
    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
    Everyone: 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.

Posting Permissions

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