Number of rows between a certain condition in vba

[mrmalum]

Hi.
Right now I have a sheet that is sectioned. Each section going down the sheet has a title that is a different size and color font than the rest of the data. Each section is also a different size. What I need is some code that will determine how many rows there are between each title so I can then use that to scan each section individually to find the number of cells with a certain color.

I guess what I'm asking is if there is a way to determine how many rows are between rows with a certain font size or color?

 

[Gary McMaster]

One of the quickest ways would probably be to use "SpecialCells". Unfortunately, cell font or color are not one of the types you can search for. You could possibly include a comment or empty cell on each row of your section titles and use it that way. Otherwise, I believe you will have to read down an entire column and check the font and/or color one cell at a time and record the addresses of the hits.

Here's a sample that assumes you have placed a comment somewhere on each row that is considered a "Section Title".

Hope it helps.

Gary

Public Sub Test()

Dim oRange As Range

Set oRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)

'xlCellTypeAllFormatConditions
'xlCellTypeAllValidation
'xlCellTypeBlanks
'xlCellTypeComments
'xlCellTypeConstants
'xlCellTypeFormulas
'xlCellTypeLastCell
'xlCellTypeSameFormatConditions
'xlCellTypeSameValidation
'xlCellTypeVisible

MsgBox oRange.Address

End Sub