PDA

View Full Version : Solved: Conditional Number Formatting



Sir Babydum GBE
02-22-2006, 02:51 AM
Hi all,

I want to format a cell in column B if the cell in A of the same row says as follows:

"Mileage" - Column B to be formatted as "General"
"Overtime" - Column B to show "Number" with two decimal places.
All other entries - Column B to be "Accounting" with "?" sign.

This can't be done with ordinary conditional formatting :( , can it be done with VBA? :dunno

Thx in advance

xld
02-22-2006, 04:25 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "Mileage": .Offset(0, 1).NumberFormat = "General"
Case "Overtime": .Offset(0, 1).NumberFormat = "#,##0.00_ ;-#,##0.00 "
Case Else: .Offset(0, 1).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Sir Babydum GBE
02-22-2006, 04:49 AM
Mr X,

I really should start paying you (my rate is ?0.76 per day - But you get an hour for lunch).

But I do have a slight problem with your code - it's Excel 97, and the entries in column A are selected from validation lists, which are not triggering the code. So...

Can the code be modified so that when a non validated entry is made anywhere on the sheet, it looks at all the cells (say from row 1 to 2,000) in column A, then compares the formatting in column B and adjusts if neccesary?

In other words. Every entry triggers the macro to go through the whole process from start to finish.

If you hate me - I'm from Yorkshire - I can take it.

xld
02-22-2006, 05:07 AM
Sir BD<

Replace that code with this. You will also need something to trigger the calculate event, so in some cell anywhere off-screen add =COUNTA($A:$A)


Private Sub Worksheet_Calculate()
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Range("A1:A" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row)
With cell
Select Case .Value
Case "Mileage": .Offset(0, 1).NumberFormat = "General"
Case "Overtime": .Offset(0, 1).NumberFormat = "#,##0.00_ ;-#,##0.00 "
Case Else: .Offset(0, 1).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Select
End With
Next cell

ws_exit:
Application.EnableEvents = True
End Sub

Sir Babydum GBE
02-22-2006, 05:25 AM
You're the best!

Sir Babydum GBE
02-22-2006, 05:30 AM
Here's a poem of thanks:

There once was a man named XLD
Who solved great conundrums whilst sipping his tea
It didn't really matter that he was from Wessex
Because in return for his favours he was forgiven by Sir BD.

The End.

Not sure it's rhythmically perfect yet, so i may make changes.

xld
02-22-2006, 05:49 AM
Here's a poem of thanks:

There once was a man named XLD
Who solved great conundrums whilst sipping his tea
It didn't really matter that he was from Wessex
Because in return for his favours he was forgiven by Sir BD.

The End.

Not sure it's rhythmically perfect yet, so i may make changes.

Couldn't resist


From the darklands of outer Wales
Came problems so perverse and so strange
That it drove young girls screaming from their homes
Many fell by the way in bloody battle
Leaving the corpulent Sir BD in wretched despair
Until he found the light at VBAX ...
Giving his problems to XL, Gibbs and D


As you can see, I don't subscribe to the rhyming school.

Aussiebear
02-22-2006, 06:19 AM
Dear Sir BD,

To think that until recently I mistakenly thought that the only good thing to come out of Yorkshire was Geoff Boycott. Then I heard of you..... so inspired was I that I raced outside and started knocking on people's front doors looking for a donation.

I'd like to say that things are looking up, but there's a bit of a problem, well two really. You see the new guy down the street, introduced me to his two german shepards. I suppose its a reasonable responce since its 1 am in the morning.

Ted

P.S. The doctor is confident that the stiches can come out in three weeks time. http://vbaexpress.com/forum/images/smilies/sad2.gif

Sir Babydum GBE
02-22-2006, 06:33 AM
From the darklands of outer Wales
Came problems so perverse and so strange
That it drove young girls screaming from their homes
Many fell by the way in bloody battle
Leaving the corpulent Sir BD in wretched despair
Until he found the light at VBAX ...
Giving his problems to XL, Gibbs and D
It's beautiful.