Solved

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

Posted on 2011-03-08
8
572 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 41

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
 
LVL 41

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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 41

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now