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

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
BSA2010Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dlmilleConnect With a Mentor Commented:
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
 
royhsiaoCommented:
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
 
dlmilleCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
BSA2010Author Commented:
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
 
dlmilleCommented:
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
 
royhsiaoCommented:
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
 
BSA2010Author Commented:
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
 
BSA2010Author Commented:
Hi Dave,

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

Thank you,

Vinny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.