CODEWITHSUNDEEP
vbaexcelmacros
zack73
zack73

Reputation: 13

VBA to format cells based on another cell not working

SOLVED: FOUND MY OWN WORKSHEET ERROR The problem I was having was trying to use two worksheet_change events in the same workbook. Because I thought that was possible, I was just renaming the worksheet event in question when I received an error, thinking nothing of it. Both my original code and the answer provided work, when combined with my other worksheet_change event. Thanks everyone.

Original Request:

I am trying to run a macro that does this:

every time cell r6 changes, run a macro that looks to see if the value in cell s9 is > or < 1, then format cells s9:t100 based on that.

I have the macro on its own to do the second part:

sub macro1()
If Range("S9").Value < 1 Then
      Range("S9:S100,T9:T100").Select
    Selection.NumberFormat = "0.0%"
Else
        Range("S9:S100,T9:T100").Select
      Selection.NumberFormat = "#,##0"
End If
end sub

This macro run on its own, works exactly as I want and formats the cells.

Then I have the worksheet event to call up that macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$R$6" Then
Call Macro1
End If
End Sub

When it is run to call up the same macro, it does not format the cells. They just stay as a % regardless of when cell r6 changes.

Any ideas why the worksheet event causes the macro to not work?

Upvotes: 0

Views: 1030

Answers (1)

PeterT
PeterT

Reputation: 8557

Try passing the worksheet object to your macro. This fully qualifies the Ranges to make sure you're working on the right area.

Also, you don't need to Select at all. Just use the range and directly change the settings.

Public Sub Macro1(ws as Worksheet)
    If ws.Range("S9").Value < 1 Then
        ws.Range("S9:S100,T9:T100").NumberFormat = "0.0%"
    Else
        ws.Range("S9:S100,T9:T100").NumberFormat = "#,##0"
    End If
end sub

Sub test()
    Macro1 ActiveSheet
End Sub

And in your Worksheet_Change...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$R$6" Then
        Macro1 Target.Worksheet
    End If
End Sub

Upvotes: 1

Related Questions