Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA conditional formatting on rows that are dynamically changed with insert and delete of prior rows

Posted on 2011-03-08
8
Medium Priority
?
586 Views
Last Modified: 2012-05-11
Hello Experts,
I am trying to utilize conditional formatting with VBA in Excel. The formatting is fairly basic, where it hides rows based on values in different cells. There is a VBA procedure that allows the user to add formatted rows in the middle of the Excel spreadsheet, thus changing the row reference. For example, if I use the following statement:
  If LCase(Range("H6").Value) = "same" Then
         Rows("28:31").EntireRow.Hidden = True
      Else
        Rows("28:31").EntireRow.Hidden = False
   End If

If a new row is added, the rows are no longer 28:31, they will be 29:32

There are two questions:

Question 1. How do you reference rows in VBA that may change its position in the spreadsheet?
Question 2. How would I use VBA to look at multiple cells for conditional formatting?

Here is the conditional formatting rules:
1. If  H6 is = SAME, then hide rows 28:31 (i.e. Questions 8-10 are hidden), else show rows 28:31
2. If  J17 AND J18 is = NO, then hide row 19:22 (i.e. Questions 2-5 are hidden), else evaluate 2a & 2b below
         a.  If  J19 is = YES, then hide row 20 (i.e. Question 3 is hidden)
         b.  If  J19 is = NO, then hide row 21:22 (i.e. Question 4 & 5 are hidden)
3. If  J33 is = NO, then hide row 34 (i.e. Hide Describe: cells below Question 12)
4. If  J35 is = NO, then hide row 36 (i.e. Hide Explain: cells below Question 13)

Attached is the sample Excel Spreadsheet.
Thanks,
Sample-v.1.xls
0
Comment
Question by:BSA2010
  • 3
  • 3
  • 2
8 Comments
 
LVL 6

Expert Comment

by:royhsiao
ID: 35075189
My battery is almost out.
Here is a possible solution for question 1.
You could use find to select the location of the question. see example.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Long, firstRow As Long, lastRow As Long
    Dim q3 As String
    Dim q4 As String


    Cells.Find(What:="question 8", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate

    q3 = Right(ActiveCell.Address, 2)
    

    Cells.Find(What:="question 10", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
   
    q4 = Right(ActiveCell.Address, 2)
    
    On Error GoTo CleanUp
    
    'Hiding Screen Updates
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

    firstRow = getStartRow()
    lastRow = getLastRow(firstRow)
    
    If LCase(Range("H6").Value) = "same" Then
        'Unprotect "crm"
        Rows(q3 & ":" & q4).EntireRow.Hidden = True
        'Rows("28:31").EntireRow.Hidden = True
      ' Protect "crm", DrawingObjects:=True, Contents:=True, Scenarios:=True
        'GoTo CleanUp
    Else
        Rows(q3 & ":" & q4).EntireRow.Hidden = False
        'Rows("28:31").EntireRow.Hidden = False
        'Rows("28:31").EntireRow.Hidden = False
    '   Unprotect "crm"
       'Protect "crm", DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If

CleanUp:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 35078839
A more generic answer to your question, in addition to what royhsiao is implying...


You can create named ranges that keep track of certain positions in the spreadsheet.  For example:

=OFFSET($A$1,MATCH(99^99,$A:$A),0) in a named range would contain the last enter in column A.

You could paste formulas in a cell to retrieve information.  For example:

  Range("A1").formula = "=Vlookup(whatever"

  then get Range("A1").value

You could also use the EVALUATE function to conduct a similar operation.

Dave
0
 

Author Comment

by:BSA2010
ID: 35085511
dlmille,

Can you give me a specific example of how this would work with named ranges? I have set up named ranges in the attached spreadsheet, but the named range doesn't move when a row is inserted or deleted.

Thanks,

Vinny
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 35085651
Pick a specific ranged name you've created where you might be moving stuff around and still want to keep track of it?  (Walk me through one step - the named range, how the user might change the spreadsheet, and what you'd want the named range to be when done) This way, we can both be more efficient..

dave
0
 
LVL 6

Expert Comment

by:royhsiao
ID: 35085809
Question 2. How would I use VBA to look at multiple cells for conditional formatting?
You could try the following code.
1) Select a range that you want to check
2) run the macro
Sub Find_ConditionalFormat()
    Dim rng As Range
        For Each rng In Selection
        If rng.FormatConditions.Count > 0 Then
            With rng.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 49407
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Else
            With rng.Interior
             .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = 0.399975585192419
                .PatternTintAndShade = 0
            End With
        End If
    Next
End Sub

Open in new window

Conditional-Formatting.JPG
0
 

Author Comment

by:BSA2010
ID: 35086782
Dave,

In the original attached spreadsheet, Rows 28-31 is a named range called "SAMEHIDE"

Rows 1-11 will remain static and not change.  The users would be inserting rows with the "Add New Record" command button located on Row 8. New rows would be inserted at Row 12, thus pushing each row down 1 every time a new record is inserted. If an inserted row is deleted, it would thus push each row up 1.

Thank you for the assistance,

Vinny
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35089505
Ok - I still fail to see what's happening or not happening per your desire.  Do you want SAME HIDE to move down as you insert above (because those same rows are being maintained by the named range - e.g., rows with Question 8-11 are still the SAME HIDE range)?

What I'm trying to say is that you have not articulated the behavior you want to happen with the range name as a result of inserting or deleting rows above that range that it currently does not do.

Please elaborate.

Dave
0
 

Author Comment

by:BSA2010
ID: 35104214
Hi Dave,

You are correct, I wasn't referencing the named range when coding the conditional formatting.

Thank you,

Vinny
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question