Solved

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

Posted on 2011-03-08
8
580 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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