Solved: Conditional Number Formatting

[Sir Babydum GBE]

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

[Bob Phillips]

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]

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.

[Bob Phillips]

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]

You're the best!

[Sir Babydum GBE]

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.

[Bob Phillips]

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]

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]

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.