Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Conditional Formatting
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default Conditional Formatting
You could use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For each cell in Me.Range("A1:A100") 'adjust to suit
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 1
cell.NumberFormat = "##""st"""
Case 2
cell.NumberFormat = "##""nd"""
Case 3
cell.NumberFormat = "##""rd"""
Case Else
cell.NumberFormat = "##""th"""
End Select
End If
Next cell
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range if need be then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 17:15:01 -0800, Jokah.D
wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Conditional Formatting
Thank you for your help Gord.
I adapted the code as follows to get the desired result with superscript:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
End Sub

Thank you once again friend and happy new year.

"Gord Dibben" wrote:

You could use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For each cell in Me.Range("A1:A100") 'adjust to suit
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 1
cell.NumberFormat = "##""st"""
Case 2
cell.NumberFormat = "##""nd"""
Case 3
cell.NumberFormat = "##""rd"""
Case Else
cell.NumberFormat = "##""th"""
End Select
End If
Next cell
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range if need be then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 17:15:01 -0800, Jokah.D
wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default Conditional Formatting
Thanks for the update.

I skipped over the "superscript" part of your original post.

Not too uncommon with me<g

But with your code the numbers are not "formatted"

You now have text numbers that cannot be calculated. Does that matter?

Another thing I could mention. Do you have to deal with decimal numbers
like 123.45?

You could change part of the code to chop those off. 123.45 would return
123rd rather than 123.45th

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
On Error GoTo endit
Application.EnableEvents = False
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
cell.Value = Int(cell.Value)
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
endit:
Application.EnableEvents = True
End Sub



Gord



On Thu, 1 Jan 2009 14:30:01 -0800, Jokah.D
wrote:

Thank you for your help Gord.
I adapted the code as follows to get the desired result with superscript:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
End Sub

Thank you once again friend and happy new year.

"Gord Dibben" wrote:

You could use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For each cell in Me.Range("A1:A100") 'adjust to suit
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 1
cell.NumberFormat = "##""st"""
Case 2
cell.NumberFormat = "##""nd"""
Case 3
cell.NumberFormat = "##""rd"""
Case Else
cell.NumberFormat = "##""th"""
End Select
End If
Next cell
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range if need be then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 17:15:01 -0800, Jokah.D
wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Conditional Formatting
There will be no decimals and the numbers require no calculation, I do
however have another problem (well 2 actually):
1) When copying to multiple sheets, only the 1st sheet works.
2) All cells in the range are tested for reformatting everytime.

I thought about using 'Activecell' to apply the formatting but unfortunately
the change event is triggered AFTER the change has taken place and the focus
has moved to the next cell which could be either the cell to the right of the
modified cell or the 1st cell of the next row.

Q. How can I reference the cell that triggered the change event as oppose to
the currently active cell?

Thank you again Gord
************************************************** *********

"Gord Dibben" wrote:

Thanks for the update.

I skipped over the "superscript" part of your original post.

Not too uncommon with me<g

But with your code the numbers are not "formatted"

You now have text numbers that cannot be calculated. Does that matter?

Another thing I could mention. Do you have to deal with decimal numbers
like 123.45?

You could change part of the code to chop those off. 123.45 would return
123rd rather than 123.45th

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
On Error GoTo endit
Application.EnableEvents = False
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
cell.Value = Int(cell.Value)
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
endit:
Application.EnableEvents = True
End Sub



Gord



On Thu, 1 Jan 2009 14:30:01 -0800, Jokah.D
wrote:

Thank you for your help Gord.
I adapted the code as follows to get the desired result with superscript:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
End Sub

Thank you once again friend and happy new year.

"Gord Dibben" wrote:

You could use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For each cell in Me.Range("A1:A100") 'adjust to suit
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 1
cell.NumberFormat = "##""st"""
Case 2
cell.NumberFormat = "##""nd"""
Case 3
cell.NumberFormat = "##""rd"""
Case Else
cell.NumberFormat = "##""th"""
End Select
End If
Next cell
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range if need be then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 17:15:01 -0800, Jokah.D
wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default Conditional Formatting
1. What are you copying to multiple sheets?

The code itself? Should work in each sheet since there are no hard-coded
sheet names. Me.Range refers to the active sheet.

2. To stop the re-formatting of all cells in the range each time a change
is made to any cell we get rid of the For..............Next. Just the
changed cell will re-format.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
If Not Application.Intersect(Me.Range("L3:L43"), Target) Is Nothing Then
With Target
If Application.IsNumber(.Value) Then
Select Case Right(.Value, 1)
Case 0
blank = True
Case 1
.Value = .Value & "st"
blank = False
Case 2
.Value = .Value & "nd"
blank = False
Case 3
.Value = .Value & "rd"
blank = False
Case Else
.Value = .Value & "th"
blank = False
End Select
If Not blank Then
n = .Characters.Count - 1
.Characters(n, 2).Font.Superscript = True
End If
End If
End With
End If
End Sub


Gord

On Thu, 1 Jan 2009 22:43:01 -0800, Jokah.D
wrote:

There will be no decimals and the numbers require no calculation, I do
however have another problem (well 2 actually):
1) When copying to multiple sheets, only the 1st sheet works.
2) All cells in the range are tested for reformatting everytime.

I thought about using 'Activecell' to apply the formatting but unfortunately
the change event is triggered AFTER the change has taken place and the focus
has moved to the next cell which could be either the cell to the right of the
modified cell or the 1st cell of the next row.

Q. How can I reference the cell that triggered the change event as oppose to
the currently active cell?

Thank you again Gord
************************************************* **********

"Gord Dibben" wrote:

Thanks for the update.

I skipped over the "superscript" part of your original post.

Not too uncommon with me<g

But with your code the numbers are not "formatted"

You now have text numbers that cannot be calculated. Does that matter?

Another thing I could mention. Do you have to deal with decimal numbers
like 123.45?

You could change part of the code to chop those off. 123.45 would return
123rd rather than 123.45th

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
On Error GoTo endit
Application.EnableEvents = False
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
cell.Value = Int(cell.Value)
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
endit:
Application.EnableEvents = True
End Sub



Gord



On Thu, 1 Jan 2009 14:30:01 -0800, Jokah.D
wrote:

Thank you for your help Gord.
I adapted the code as follows to get the desired result with superscript:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
End Sub

Thank you once again friend and happy new year.

"Gord Dibben" wrote:

You could use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For each cell in Me.Range("A1:A100") 'adjust to suit
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 1
cell.NumberFormat = "##""st"""
Case 2
cell.NumberFormat = "##""nd"""
Case 3
cell.NumberFormat = "##""rd"""
Case Else
cell.NumberFormat = "##""th"""
End Select
End If
Next cell
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range if need be then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 17:15:01 -0800, Jokah.D
wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4
Default Conditional Formatting
Silly me! I deleted a row from above the grid and a couple from the middle to
make it look better and didn't change the range accordingly! The range starts
from L2:L40 so as I was testing it on L2 the code wasn't being processed. Doh!

All is working like a dream now, you've been a great help Gord, thank you
very much.

************************************************** **********

"Gord Dibben" wrote:

1. What are you copying to multiple sheets?

The code itself? Should work in each sheet since there are no hard-coded
sheet names. Me.Range refers to the active sheet.

2. To stop the re-formatting of all cells in the range each time a change
is made to any cell we get rid of the For..............Next. Just the
changed cell will re-format.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
If Not Application.Intersect(Me.Range("L3:L43"), Target) Is Nothing Then
With Target
If Application.IsNumber(.Value) Then
Select Case Right(.Value, 1)
Case 0
blank = True
Case 1
.Value = .Value & "st"
blank = False
Case 2
.Value = .Value & "nd"
blank = False
Case 3
.Value = .Value & "rd"
blank = False
Case Else
.Value = .Value & "th"
blank = False
End Select
If Not blank Then
n = .Characters.Count - 1
.Characters(n, 2).Font.Superscript = True
End If
End If
End With
End If
End Sub


Gord

On Thu, 1 Jan 2009 22:43:01 -0800, Jokah.D
wrote:

There will be no decimals and the numbers require no calculation, I do
however have another problem (well 2 actually):
1) When copying to multiple sheets, only the 1st sheet works.
2) All cells in the range are tested for reformatting everytime.

I thought about using 'Activecell' to apply the formatting but unfortunately
the change event is triggered AFTER the change has taken place and the focus
has moved to the next cell which could be either the cell to the right of the
modified cell or the 1st cell of the next row.

Q. How can I reference the cell that triggered the change event as oppose to
the currently active cell?

Thank you again Gord
************************************************* **********

"Gord Dibben" wrote:

Thanks for the update.

I skipped over the "superscript" part of your original post.

Not too uncommon with me<g

But with your code the numbers are not "formatted"

You now have text numbers that cannot be calculated. Does that matter?

Another thing I could mention. Do you have to deal with decimal numbers
like 123.45?

You could change part of the code to chop those off. 123.45 would return
123rd rather than 123.45th

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
On Error GoTo endit
Application.EnableEvents = False
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
cell.Value = Int(cell.Value)
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
endit:
Application.EnableEvents = True
End Sub



Gord



On Thu, 1 Jan 2009 14:30:01 -0800, Jokah.D
wrote:

Thank you for your help Gord.
I adapted the code as follows to get the desired result with superscript:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Dim blank As Boolean
For Each cell In Me.Range("L3:L43")
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 0
blank = True
Case 1
cell.Value = cell.Value & "st"
blank = False
Case 2
cell.Value = cell.Value & "nd"
blank = False
Case 3
cell.Value = cell.Value & "rd"
blank = False
Case Else
cell.Value = cell.Value & "th"
blank = False
End Select
If Not blank Then
n = cell.Characters.Count - 1
cell.Characters(n, 2).Font.Superscript = True
End If
End If
Next cell
End Sub

Thank you once again friend and happy new year.

"Gord Dibben" wrote:

You could use sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For each cell in Me.Range("A1:A100") 'adjust to suit
If Application.IsNumber(cell.Value) Then
Select Case Right(cell.Value, 1)
Case 1
cell.NumberFormat = "##""st"""
Case 2
cell.NumberFormat = "##""nd"""
Case 3
cell.NumberFormat = "##""rd"""
Case Else
cell.NumberFormat = "##""th"""
End Select
End If
Next cell
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

Edit the range if need be then Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Wed, 31 Dec 2008 17:15:01 -0800, Jokah.D
wrote:

I would like to represent numbers as placements for example: 421 becomes
421st with the "st" in superscript, 32 becomes 32nd with the "nd" in
superscript.

Is there a way to do this in Excel 2007?






Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting cells in a column with conditional formatting? shamor Excel Discussion (Misc queries) 8 May 19th 08 10:11 PM
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 3 January 20th 07 02:02 PM
conditional Formatting based on cell formatting Totom Excel Worksheet Functions 0 January 15th 07 04:35 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM


All times are GMT +1. The time now is 11:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement