Macro Help

[ratanner]

I am new to Macros and am having a bit of a hard time with two issues. I am using Excel 2010 and am working on a spreadsheet.

The first problem I am having is trying to write a Macro in VBA that will hide a particular row of cells given a criteria. Cell U3 is my reference and contains a formula to give me either a 1 or a 2 based upon another cells information. In other words, if cell B2=white, yellow, or blue, cell U3 will give me a 1. If B2 = anything else cell U3 will give me a 2.

If U3=1 I am wanting to hide rows 4-7 but if U3=2 I want rows 4-7 to be unhiden.


The second problem I am having is on a separate worksheet. On the second worksheet, I am wanting to take and merge two cells once the total of one cell = 0
Example is I have cells I4 and I5. I4 is the total number of miles driven in a day and I5 is a countdown of remaining miles allowed. What I am wanting to do is once I5 = 0 I want I4 and I5 to merge and only show the contents of I4.

Is what I am asking for possible or is it to complex? I have always come to Mr. Excel forums for assistance and everyone in here is wonderful for being able to offer up help when I need it. Thank you in advance for your help.

 

[Misca]

Here's a couple of short macros to guide you in the right direction:

Sub HideRows()

Dim Boo As Boolean
Dim X As Integer    'This macro uses this to change the visibility trigger value between 1 and 2


Select Case Range("U3").Value
    Case Is = 1
       Boo = True
       X = 2
    Case Else
        Boo = False
        X = 1
End Select


Range("A4:A7").EntireRow.Hidden = Boo


Range("U3").Value = X   'Changes the U3 value


End Sub




Sub MergeCells()


Dim c As Range


'Doesn't show alerts
Application.DisplayAlerts = False


'Checks the cells from given range
For Each c In Range("A5:I5")
    With c
'Makes sure the cell is not already merged:
        If .MergeCells = False Then
'If the cell is not empty and the value is 0:
            If .Value = 0 And .Value <> "" Then
                .Offset(-1).Resize(2).Merge
            End If
        End If
    End With
Next c


End Sub

 

[ratanner]

I may be doing it all wrong. I right clicked the tab I wanted and clicked view code. When VBA opened I pasted the first half into the code and nothing happened. I don't know what I am doing wrong.



I also tried this to reference a different row of cell

Private Sub Worksheet_Change(ByVal target As Range)


If target.Address <> "$U$7" Then Exit Sub
Select Case Range("U7").Value
Case Is = 1: Rows("8:11").EntireRow.Hidden = True
Case Is = 2: Rows("8:11").EntireRow.Hidden = False
Case Else:
End Select


End Sub

The problem I am encountering is that I have to manually click the reference cell and hit enter to get it to work. I'm needing it to go automatically once the number changes.

 

[Misca]

If U7 is a formula you're going to need to use different Target address (=the input cells for the formula) to fire the macro but you can still refer to the cell to see if the rows should be hidden or not. Formula calculations do not trigger worksheet change (or any other) events.

You could also try using the Selection Change event. This might slow the sheet down though because it runs the macro whenever you select a different cell on the sheet.