Conditional Format next question

[technix]

with the generous help of Mudface I have conquered my Conditional Format for line coloring, Now is it possible if cell G3 = Cash to Format the text to be bold, and ALL Caps when it changes the line color
Thank you in advance.

[Mark W.]

"ALL Caps" is not a formatting option. Changing a character from lower to upper case is a change in the value itself (e.g., changing "a" to "A" alters the ASCII representation of the original value from 97 to 65.) Excel formatting never changes the contents of a cell -- only how it's displayed.

[technix]

With that in mind there wouldnt be a way to have it change that value then. So that whatever text(not #'s) was entered would make them all caps?

thank you

[Mark W.]

Conditional formatting would not do it, but perhaps some one could write a macro for you.

[ This Message was edited by: Mark W. on 2002-05-24 13:07 ]

[Tom Urtis]

Would this help you? Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$G$3" Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value <> "Cash" Then
Target.Font.Bold = False
Else
With Target
.Value = "CASH"
.Font.Bold = True
End With
End If
End Sub

Notice a quirk that can be modified -- if anyone enters "CASH" in G3 then that will not trigger the macro...I interpreted your post as if "Cash" is the prevalent entry.

Also, I assumed the only time you want G3 bold is if "Cash" is entered (yielding "CASH").

I'm not familiar with your original post, so this code may affect your conditional formatting for the line coloring. If so, please post a follow-up.

HTH

[Joe Was]

You can use the UCase method to change any input to all upper-case text. Here are a few ways:

Private Sub Worksheet_Change(ByVal Target As Range)
'Worksheet module automatic code.
'All text in columns B&C to upper-case.

Dim rCells As Range
Application.EnableEvents = False
If Not Intersect(Target, Columns("B:C")) Is Nothing Then
For Each rCells In Intersect(Target, Columns("B:C"))
rCells = UCase(rCells.Text)
Next
End If
Application.EnableEvents = True
End Sub

Sub myOneUCase()
'Module code, make selected cell upper-case.

Dim rCells As Range
Set rCells = Selection.Cells
Selection = UCase(rCells)
End Sub


Sub myBold()
'Make selected cell, bold.

Selection.Font.Bold = True
End Sub


Sub ToggleCase()
'Make selected upper-case if now lower-case.
'Make selected lower-case if upper-case now.

Dim rng As Range
For Each rng In Selection.Cells
Select Case True
Case rng = LCase(rng)
rng = UCase(rng)
Case rng = UCase(rng)
rng = Application.Proper(rng)
Case Else
rng = LCase(rng)
End Select
Next
End Sub


Sub myOneLCase()
'Make selection lower-case.

Dim rCells As Range
Set rCells = Selection.Cells
Selection = LCase(rCells)
End Sub

Sub myRegular()
' by Joe Was
'Make coded range regular text, reset text options.

Range("A1:H20").Select
Application.CutCopyMode = False
With Selection.Font
.FontStyle = "Regular"
End With
'Range("A23").Select
End Sub

You can add these codes to other macros or use them as they are. Hope this helps. JSW