Conditional Numerical Formatting

I'd like the number format for the value in cell a3 to be a general number if
a1 is empty and i'd like the format to be a percentage if a1 contains a
value.

Since one cell can't change another cell's formatting, this will require
an Event macro.

Put this in your worksheet code module (right-click on the worksheet tab
and choose "View Code")"


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target(1)
If .Address(False, False) = "A1" Then
If IsEmpty(.Value) Then
Range("A3").NumberFormat = "General"
Else
Range("A3").NumberFormat = "0%"
End If
End If
End With
End Sub





In article <ECE08CF5-8C3E-4F46-BEA4-E7F8F7DA2186@microsoft.com>,
Qaspec <Qaspec@discussions.microsoft.com> wrote:

> I'd like the number format for the value in cell a3 to be a general number if
> a1 is empty and i'd like the format to be a percentage if a1 contains a
> value.
>
>

What if A1 was on sheet1("Data") and A3 were on sheet2("Daily")? How would I
change the code?

"JE McGimpsey" wrote:

> Since one cell can't change another cell's formatting, this will require
> an Event macro.
>
> Put this in your worksheet code module (right-click on the worksheet tab
> and choose "View Code")"
>
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target(1)
> If .Address(False, False) = "A1" Then
> If IsEmpty(.Value) Then
> Range("A3").NumberFormat = "General"
> Else
> Range("A3").NumberFormat = "0%"
> End If
> End If
> End With
> End Sub
>
>
>
>
>
> In article <ECE08CF5-8C3E-4F46-BEA4-E7F8F7DA2186@microsoft.com>,
> Qaspec <Qaspec@discussions.microsoft.com> wrote:
>
> > I'd like the number format for the value in cell a3 to be a general number if
> > a1 is empty and i'd like the format to be a percentage if a1 contains a
> > value.
> >
> >
>

Put the code in the Data sheets
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
set sh = Worksheets("Daily")
With Target(1)
If .Address(False, False) = "A1" Then
If IsEmpty(.Value) Then
sh.Range("A3").NumberFormat = "General"
Else
sh.Range("A3").NumberFormat = "0%"
End If
End If
End With
End Sub

--
Regards,
Tom Ogilvy

"Qaspec" <Qaspec@discussions.microsoft.com> wrote in message
news:C1884DE0-A816-4B6F-B2E4-250ECFE46185@microsoft.com...
> What if A1 was on sheet1("Data") and A3 were on sheet2("Daily")? How would
I
> change the code?
>
> "JE McGimpsey" wrote:
>
> > Since one cell can't change another cell's formatting, this will require
> > an Event macro.
> >
> > Put this in your worksheet code module (right-click on the worksheet tab
> > and choose "View Code")"
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > With Target(1)
> > If .Address(False, False) = "A1" Then
> > If IsEmpty(.Value) Then
> > Range("A3").NumberFormat = "General"
> > Else
> > Range("A3").NumberFormat = "0%"
> > End If
> > End If
> > End With
> > End Sub
> >
> >
> >
> >
> >
> > In article <ECE08CF5-8C3E-4F46-BEA4-E7F8F7DA2186@microsoft.com>,
> > Qaspec <Qaspec@discussions.microsoft.com> wrote:
> >
> > > I'd like the number format for the value in cell a3 to be a general
number if
> > > a1 is empty and i'd like the format to be a percentage if a1 contains
a
> > > value.
> > >
> > >
> >