Results 1 to 6 of 6

Thread: Noobie seeking basic help with conditional formatting formula

  1. #1

    Noobie seeking basic help with conditional formatting formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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...

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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:

    Code:
    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


  3. #3
    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???

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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....


  5. #5
    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.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,369
    Articles
    0
    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.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •