Counting cells with border or font in a specific row

[spiffmonkey1]

Hi,

So I need a macro that will allow me to count the number of cells that contain a certain font or border so that I can set that count to a specific variable.


Thanks

 

[Weaver]

This counts cells with Arial font or a border at the bottom.

You ought to be able to adapt the idea for what you need

Sub countfont()
    Dim f As Long, c As Range, b As Long
    For Each c In ActiveSheet.UsedRange
        If c.Font.Name = "Arial" Then f = f + 1
        If c.Borders(xlEdgeBottom).LineStyle <> xlNone Then b = b + 1
    Next c
    MsgBox "Font count: " & f & vbCrLf & "Border Count: " & b
End Sub

HTH

 

[spiffmonkey1]

Hi, Weaver

Thanks for helping again but I don't understand how I can use this with my code. I am still a beginner at excel so I don't understand how to set "UsedRange". I also do not understand how I can use this to get a cell count by saying Then f = f + 1 or saying Then b = b + 1.

I am also trying to compile the code similar to the one u mentioned above and its coming as Compile Error- Invalid Next control variable reference.

 

[Weaver]

1. you don't need to set 'usedrange', it's a built in range that represents the extent of the cells used in the current worksheet.

2. In the example, f is the count of cells with the font "arial" and b is the count of cells with a bottom border.

These are just examples of how you could utilise the code. If you need me to code it for you exactly, you're going to have to tell me what you're looking for and where.

 

[spiffmonkey1]

Ok so part of my code is like this-

For r = 4 To FinalColumn
'1 box colored G=G
If WorksheetFunction.CountIf(Range(Cells(70, r), Cells(81, r)), "yes") = 1 _
And WorksheetFunction.CountIf(Range(Cells(70, r), Cells(81, r)), "N/A") = 8 _
And WorksheetFunction.CountBlank(Range(Cells(70, r), Cells(81, r))) = 3 Then
Range(Cells(68, r), Cells(68, r)).Interior.ColorIndex = 50 'Background color of cell
Range(Cells(68, r), Cells(68, r)).Font.ColorIndex = 1 'Font color of cell
Range(Cells(68, r), Cells(68, r)) = "Green" 'Text in the cell
Range(Cells(68, r), Cells(68, r)).BorderAround Weight:=xlThick 'Thick border around the cell

End If
Next r
End Sub

I wanted FinalColumn to equal to the number of cells with border or any format in the range.

 

[spiffmonkey1]

Ok I got it to work by doing-
Dim c As Range, b As Long
For Each c In ActiveSheet.Range("D65:AIU65")
If c.Borders(xlEdgeBottom).LineStyle <> xlNone Then b = b + 1
Next c
MsgBox "Font count: " & f & vbCrLf & "Border Count: " & b
FinalColumn = b

Thanks for being patient and helpful!

 

[chopperj]

Hi,

So I need a macro that will allow me to count the number of cells that contain a certain font or border so that I can set that count to a specific variable.


Thanks

try this

Sub cell_boarder_count()
'Chopperj @ MrExcel
Dim bordcell As Range
Dim bordercount As Double

bordercount = 0 'set counter to zero

For Each bordcell In Range("g521:i523") 'enter your range here
If bordcell.Borders.Weight = xlMedium Then bordercount = bordercount + 1 'i recorded macro to id border type
Next bordcell
MsgBox "you have " & bordercount & " with a border", vbOKOnly, "border count"
End Sub


let me know how you get on...