Noobie seeking basic help with conditional formatting formula

[texaslonghorn]

Hi all. Disclaimer - please bear with me, I ain't too smart and am a real beginner with excel, so my questions may cause severe "eye rolling" to occur...

I am using excel 2011 for Mac and am trying to get a range of cells (for ease of discussion, call them A5 through B16 to become shaded if C4="N". The purpose here is to grey out a range of cells if the user answers "N" to the question about the applicability of the section.

Similarly, if C4="N", is it possible for rows 5 through 15 to collapse or somehow else be hidden - but not deleted, with rows 16 and beyond shifting up?

Thanks for all the help...

[NBVC]

For Conditionally formatting the cells, first select the range to affect.. then go to Home|Conditional foratting and select New Rule. Then select (use a formula to determine which cells to format) from the top section and enter

=$C$4="N"

Click Format and choose for the appropriate tabs to get desired effects.

To hide the rows, you will need VBA and an event macro.

To do this, right-click the tab of the current sheet (at the bottom) and select View Code, then paste the following into the editor Window:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$C$4" Then
            On Error Resume Next

            Application.EnableEvents = False
            If UCase(Target.Value) = "N" Then
                Range("5:15").Rows.EntireRow.Hidden = True
            Else
                Range("5:15").Rows.EntireRow.Hidden = False
            End If
            
            Target.Activate
            Application.EnableEvents = True
            
            On Error GoTo 0
    End If

End Sub

and close the window.

Then test by changing the values in C4

[texaslonghorn]

Fantastic - thanks. The conditional formatting worked perfectly.

But I couldn't access the code by right clicking on the page tab. Do I have to do something to tell Excel that I want to be able to use VBA on the sheet to begin with?

Maybe it is some Mac thing???

[NBVC]

I know that with Excel 2008 for Mac you could not have any macros. But I believe that 2011 version has restored the functionality.

If you go to Developer tab and select Visual Basic or (ALT+F11).

Then locate your workbook in the Project explorer in the left pane, expand it to see the sheetnames, double-click on the sheet of interest and paste code there.

Unfortunately I never used a MAC for Excel so not familiar with the nuances....

[texaslonghorn]

okay - thanks. I did get to the VBA menu and was able to add the code - it kinda worked but not real smooth. I had to move the cursor around to get it to actually hide ore redisplay the rows, then when they did reappear, they were highlighted as if I had selected them all. All in all, not slick enough for what I need, so I will just stick to the conditional formatting. thanks to all for quick responses that worked.

[NBVC]

It must me a Mac Excel thing... for me it works very nicely... in Excel 2003 as well as in Excel 2007. You type an "N" in C4 and rows 5:15 get hidden, and C4 remains active cell. Then you type anything else in C4, rows 5:15 reappear without a hitch and C4 is still active cell.... so not sure if it is a Mac issue or not.