Function help

[pujo]

I am trying to count the number of cells in a range that have a red border.
Tried different ways but something just isn't right. I can't get it to work.
Here is what I have.
Thanks for the help
PuJo

Option Explicit


Public Function CountRedBorderedCells(MyRange As Range) As Integer

    '=CountRedBorderedCells(B11:H16) (formula I use in the cell)

    Dim MyBorderCount As Integer
    Dim c As Variant
    
    For Each c In MyRange
    
        If c.Borders(xlEdgeLeft).Borders.ColorIndex = 3 And _
           c.Borders(xlEdgeRight).Borders.ColorIndex = 3 And _
           c.Borders(xlEdgeTop).Borders.ColorIndex = 3 And _
           c.Borders(xlEdgeBottom).Borders.ColorIndex = 3 Then
            
            MyBorderCount = MyBorderCount + 1
        End If
    Next c
    
    CountRedBorderedCells = MyBorderCount
    
End Function

 

[JoeMo]

Close, but I think your syntax is off. Try:

Public Function CountRedBorderedCells(MyRange As Range) As Integer
    Dim MyBorderCount As Integer
    Dim c As Variant
    For Each c In MyRange
        If c.Borders(xlEdgeLeft).ColorIndex = 3 And _
           c.Borders(xlEdgeRight).ColorIndex = 3 And _
           c.Borders(xlEdgeTop).ColorIndex = 3 And _
           c.Borders(xlEdgeBottom).ColorIndex = 3 Then
            
            MyBorderCount = MyBorderCount + 1
        End If
    Next c
    CountRedBorderedCells = MyBorderCount
End Function

 

[Fluff]

You need to remove the Borders from infront of ColorIndex.

 

[pujo]

Thanks guys!

 

[Fluff]

However you can also simplify it slightly like

        If c.Borders.ColorIndex = 3 Then
            MyBorderCount = MyBorderCount + 1
        End If

 

[pujo]

I thought that I had to account for all sides of the cell as to not get the logic confised with another cell that only has one side bordered or something...

 

[Fluff]

No need to check all the edges, as that's what Borders does if you don't specify an edge.