Solved

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

Posted on 2011-03-08
8
582 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

615 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