Is the VISIBLE cell above activecell bold?

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
I need to test to see if the VISIBLE cell that appears to be directly above my ACTIVE cell is bold or not.

I tried using activecell.offset(-1,0) but that doesn't work since there is often a hidden row where this may or may not be true.

I have no idea what row the next visible row will be.

Sorry if I'm missing something dumb, I've been working on this one issue for six hours and I am a mess. At least I finally figured out the problem.

Any help appreciated.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Being hid should not matter. Please post your script here.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,824
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Probably not thinking straight as it is a bit ugly but try
Code:
Sub xxx()
    Dim i As Long
    For i = ActiveCell.Row - 1 To 1 Step -1
        If Rows(i).EntireRow.Hidden = False Then

            If Cells(i, ActiveCell.Column).Font.Bold = True Then
                MsgBox "THE CELL IS BOLD"
            Else
                MsgBox "IT AIN'T BOLD"
            End If
            Exit Sub
        Else
        End If
    Next
End Sub

or if you don't mind the activecell changing

Code:
Sub CCC()
    ActiveCell.Offset(-1, 0).Activate

    Do Until Selection.EntireRow.Hidden = False
        If Selection.EntireRow.Hidden = True Then
            ActiveCell.Offset(-1, 0).Activate
        End If
    Loop
    If ActiveCell.Font.Bold = True Then
        MsgBox "THE CELL IS BOLD"
    Else
        MsgBox "IT AIN'T BOLD"
    End If
End Sub
 

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
Being hid should not matter. Please post your script here.

Here it is.

Code:
 If ActiveCell.Offset(-1, 0).Font.Bold = True Then Range(ActiveCell, ActiveCell.Offset(-1, 0)).EntireRow.Hidden = True


Sorry it took me so long to reply, I had to get away from the computer before I gave into temptation and flung it out the window.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,671
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Here is a non-looping method to find the answer to your question...
Code:
Sub IsVisibleCellAboveActiveCellBold()
  With Range(Cells(1, ActiveCell.Column), ActiveCell.Offset(-1)).SpecialCells(xlVisible)
    If .Areas(.Areas.Count)(.Areas(.Areas.Count).Rows.Count).Font.Bold Then
      MsgBox "It's bold!"
    Else
      MsgBox "It's not bold!"
    End If
  End With
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
If ActiveCell.Offset(-1, 0).Font.Bold = True Then ActiveCell.Offset(-1).Rows.Hidden = True
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,671
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Here it is.
Code:
 If ActiveCell.Offset(-1, 0).Font.Bold = True Then Range(ActiveCell, ActiveCell.Offset(-1, 0)).EntireRow.Hidden = True
I think this is a good point to ask you... what is the ultimate goal of code procedure this it in; that is, exactly what is the entire code procedure supposed to be doing?
 

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
This one worked perfectly. I did need the activecell to remain the same. Thank you so much! You solved a problem I have been working on all day.

Probably not thinking straight as it is a bit ugly but try
Code:
Sub xxx()
    Dim i As Long
    For i = ActiveCell.Row - 1 To 1 Step -1
        If Rows(i).EntireRow.Hidden = False Then

            If Cells(i, ActiveCell.Column).Font.Bold = True Then
                MsgBox "THE CELL IS BOLD"
            Else
                MsgBox "IT AIN'T BOLD"
            End If
            Exit Sub
        Else
        End If
    Next
End Sub

or if you don't mind the activecell changing

Code:
Sub CCC()
    ActiveCell.Offset(-1, 0).Activate

    Do Until Selection.EntireRow.Hidden = False
        If Selection.EntireRow.Hidden = True Then
            ActiveCell.Offset(-1, 0).Activate
        End If
    Loop
    If ActiveCell.Font.Bold = True Then
        MsgBox "THE CELL IS BOLD"
    Else
        MsgBox "IT AIN'T BOLD"
    End If
End Sub
 

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
I have a few thousand rows, and the last column of each row says TRUE or FALSE. Then my macro inserts subtotals based on a different column, and now the last column has BLANKS on the rows containing subtotals.

Then there is a loop that goes through each of the TRUE or FALSE or BLANK cells and hides the entire row if the value in the last column is FALSE. That worked great, but when the loop was done and the rows were hidden I was left with the subtotal rows for groups where everything else in the subtotal range was hidden.

I had also bolded the heading row, so, conveniently enough, if the visible row on top of the activecell was bold, then I know that subtotal row contains no TRUE rows and that the subtotal row should be hidden. I also want to hide the first visible row that was above the active cell because that contains the heading row.

looks like this when I need to run the requested code:

group1 TRUE
1a TRUE
1c TRUE
1d TRUE
subtotal1
group2 TRUE
subtotal2 (activecell here)



Sorry I can't post the real spreadsheet, it's extremely confidential

I think this is a good point to ask you... what is the ultimate goal of code procedure this it in; that is, exactly what is the entire code procedure supposed to be doing?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top