Conditional formatting ? ? ? ? NT
 

Op woensdag 25 maart 2009 21:36:19 UTC+1 schreef Rick Rothstein:
See if this event procedure code works better for you...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range) Dim X As Long
Dim R As Range
On Error Resume Next
For Each R In Target
For X = 1 To Len(R.Value)
Select Case AscW(Mid(R.Value, X, 1))
Case 9824 'Spade symbol
R.Characters(X, 1).Font.ColorIndex = 5
Case 9827 'Club symbol
R.Characters(X, 1).Font.ColorIndex = 10
Case 9829 'Heart symbol
R.Characters(X, 1).Font.ColorIndex = 3
Case 9830 'Diamond symbol
R.Characters(X, 1).Font.ColorIndex = 46
Case Else 'No Trump symbol
R.Characters(X, 1).Font.ColorIndex = xlColorIndexAutomatic
End Select
If X 1 Then
If Mid(R.Value, X - 1, 2) = "NT" Then
R.Characters(X - 1, 2).Font.ColorIndex = 44
End If
End If
Next
Next
End Sub

--
Rick (MVP - Excel)


"Pierre62" wrote in message
...
Hello Rick,

I still have problems with the code you gave me.
Not the macro but the other one.
I made a new workbook with one worksheet.
I have put the non-macro in the ThisWorkbook sheet.
I deleted several comluns at one time and then Excel is working
over and over.
When I hit the Esc key I select the "debug/error" button (I work
with a duch
version) this line is colored yellow:

For X = 1 To Len(R.Value)

so I suppose there is the reason why it takes so long.
I hope you have the same and will be able to fix it.
If you don't have it, do you have any idea what the problem could
be?

Pierre

This is the code I use:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target
As Range)
Dim X As Long
Dim R As Range
For Each R In Target
R.Characters.Font.ColorIndex = xlColorIndexAutomatic
For X = 1 To Len(R.Value)
Select Case AscW(Mid(R.Value, X, 1))
Case 9824 'Spade symbol
R.Characters(X, 1).Font.ColorIndex = 23
Case 9827 'Club symbol
R.Characters(X, 1).Font.ColorIndex = 10
Case 9829 'Heart symbol
R.Characters(X, 1).Font.ColorIndex = 3
Case 9830 'Diamond symbol
R.Characters(X, 1).Font.ColorIndex = 45
End Select
If X 1 Then 'SA text
If Mid(R.Value, X - 1, 2) = "SA" Then
R.Characters(X - 1, 2).Font.ColorIndex = 7
End If
End If
Next
Next
End Sub

Hello all,

I worked for years with this code and I am still very happy with it,
but now I have a new partner and he does not use Excel but Word. I
tried to use this code in Word, but as there are no cells in Word, it
did not work (probably more reasons...).

Is there anyone who can change it in order to work in Word (2010 /
2013) please?

In advance, thank you very much.
Pierre

You might get better replies asking in a 'Word' forum. IMO, though,
your partner should drop using Word and use Excel instead. Once you
turn off 'gidlines' display it looks like a document writer!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Conditional formatting ? ? ? ? NT
 

typo...


You might get better replies asking in a 'Word' forum. IMO, though,
your partner should drop using Word and use Excel instead. Once you

turn off 'gridlines' display it looks like a document writer!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Conditional formatting ? ? ? ? NT
 

Op woensdag 24 september 2014 01:37:17 UTC+2 schreef GS:
Op woensdag 25 maart 2009 21:36:19 UTC+1 schreef Rick Rothstein:

See if this event procedure code works better for you...



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As

Range) Dim X As Long

Dim R As Range

On Error Resume Next

For Each R In Target

For X = 1 To Len(R.Value)

Select Case AscW(Mid(R.Value, X, 1))

Case 9824 'Spade symbol

R.Characters(X, 1).Font.ColorIndex = 5

Case 9827 'Club symbol

R.Characters(X, 1).Font.ColorIndex = 10

Case 9829 'Heart symbol

R.Characters(X, 1).Font.ColorIndex = 3

Case 9830 'Diamond symbol

R.Characters(X, 1).Font.ColorIndex = 46

Case Else 'No Trump symbol

R.Characters(X, 1).Font.ColorIndex = xlColorIndexAutomatic

End Select

If X 1 Then

If Mid(R.Value, X - 1, 2) = "NT" Then

R.Characters(X - 1, 2).Font.ColorIndex = 44

End If

End If

Next

Next

End Sub



--

Rick (MVP - Excel)





"Pierre62" wrote in message

...

Hello Rick,



I still have problems with the code you gave me.

Not the macro but the other one.

I made a new workbook with one worksheet.

I have put the non-macro in the ThisWorkbook sheet.

I deleted several comluns at one time and then Excel is working

over and over.

When I hit the Esc key I select the "debug/error" button (I work

with a duch

version) this line is colored yellow:



For X = 1 To Len(R.Value)



so I suppose there is the reason why it takes so long.

I hope you have the same and will be able to fix it.

If you don't have it, do you have any idea what the problem could

be?



Pierre



This is the code I use:



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target

As Range)

Dim X As Long

Dim R As Range

For Each R In Target

R.Characters.Font.ColorIndex = xlColorIndexAutomatic

For X = 1 To Len(R.Value)

Select Case AscW(Mid(R.Value, X, 1))

Case 9824 'Spade symbol

R.Characters(X, 1).Font.ColorIndex = 23

Case 9827 'Club symbol

R.Characters(X, 1).Font.ColorIndex = 10

Case 9829 'Heart symbol

R.Characters(X, 1).Font.ColorIndex = 3

Case 9830 'Diamond symbol

R.Characters(X, 1).Font.ColorIndex = 45

End Select

If X 1 Then 'SA text

If Mid(R.Value, X - 1, 2) = "SA" Then

R.Characters(X - 1, 2).Font.ColorIndex = 7

End If

End If

Next

Next

End Sub



Hello all,



I worked for years with this code and I am still very happy with it,

but now I have a new partner and he does not use Excel but Word. I

tried to use this code in Word, but as there are no cells in Word, it

did not work (probably more reasons...).



Is there anyone who can change it in order to work in Word (2010 /

2013) please?



In advance, thank you very much.

Pierre



You might get better replies asking in a 'Word' forum. IMO, though,

your partner should drop using Word and use Excel instead. Once you

turn off 'gidlines' display it looks like a document writer!<g



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hello GS,

that's what I did some 2 months ago, but no reply at all there.

Pierre

Conditional formatting ? ? ? ? NT
 

Hello GS,

that's what I did some 2 months ago, but no reply at all there.

Pierre

Not surprised since both Word/Excel have entirely different object
models. Unless someone there is reasonably proficient at programming
both, you're out of luck! There are readers here that use/program both
and so perhaps someone will happen upon your post and provide a
solution.

Meanwhile, IMO, it's likely that your new partner can learn to use your
Excel version if you make the few changes in Options required to make
the display look/feel like a document writer. Managing page width is
about the hardest part of using Excel as a word processor, but once
handled there's nothing a word processor can do that can't be done or
duplicated in Excel (or Calc for that matter)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion