Consulting

Results 1 to 5 of 5

Thread: Conditional Formatting borders around cells with formulas

  1. #1
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location

    Conditional Formatting borders around cells with formulas

    I am trying to add conditional formatting borders and to highlight every other row around all cells containing data (A3:N100) but the conditional formatting see the formulas in the cells and adds borders around the entire range (A3:N100).Is there a way to add conditional formatting borders and to highlight every other row around cells only containing data?

    Thank you for any and all help I am running Excel 2007

  2. #2

    Possible solution via VBA

    Slightly re-jigging some old code which I used to spot cells with formulae, instead of selecting them, I added a border (which is what you seem to want). Its no beautiful code, but works. Just don't select the entire sheet, 'cos it'll go through every cell to check them all.

    Sub HasFormulas2()
    Dim Addrss As String
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a valid Range to use this Function", vbOKOnly, "Cool Tools !!"
        Exit Sub
    End If
    Select Case Selection.HasFormula
    Case True
        MsgBox "All cells in the selected range have formulas", vbOKOnly, _
            "Does this range have formulas ?"
    Case False
        MsgBox "All cells in the selected range do not have formulas", vbOKOnly, _
            "Does this range have formulas ?"
    Case Else
    '    MsgBox "Some of the cells in the selected range have formulas", vbOKOnly, _
    '        "Does this range have formulas ?"
        Addrss = ""
        For Each sell In Selection
            If sell.HasFormula Then
                If Addrss = "" Then
                    Addrss = sell.Address
                Else
                    Addrss = Addrss & "," & sell.Address
                End If
            End If
        Next
        Range(Addrss).Borders.LineStyle = xlContinuous
    End Select
    End Sub






    Quote Originally Posted by oam View Post
    I am trying to add conditional formatting borders and to highlight every other row around all cells containing data (A3:N100) but the conditional formatting see the formulas in the cells and adds borders around the entire range (A3:N100).Is there a way to add conditional formatting borders and to highlight every other row around cells only containing data?

    Thank you for any and all help I am running Excel 2007
    Last edited by Aussiebear; 11-22-2022 at 11:32 PM. Reason: Added code tags to supplied code

  3. #3
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location
    ashleymoore,

    Thank you your response but it is not what I was looking for, I had to use another method.

    I do appreciate you responding, thank you

  4. #4

    How did you do it?

    It'd be good to know the approach you used. Can you outline please?
    thanks,
    Ashley

  5. #5
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location
    I used code to copy values from one worksheet to another. This allowed the conditional formatting to work properly due to no formulas in the cells.Hope this helps.

Posting Permissions

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