HI,
How do i can change the font size with a condition. If A1 is more
than 75 characters then the font size to reduce to 8. otherwise the
font size to remain 10.
Any suggestions?
thanks
regards
NOWFAL.


--
nowfal
------------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=395535

The font size is not exposed to conditional formatting, but you can do it
with event code.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
If .Value 75 Then
.Font.Size = 8
Else
.Font.Size = 10
End If
End If
End With

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.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"nowfal" wrote in
message ...

HI,
How do i can change the font size with a condition. If A1 is more
than 75 characters then the font size to reduce to 8. otherwise the
font size to remain 10.
Any suggestions?
thanks
regards
NOWFAL.


--
nowfal
------------------------------------------------------------------------
nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
View this thread: http://www.excelforum.com/showthread...hreadid=395535

If I want it to apply for the entire col A, or the entire sheet,
how could your sub be amended ?
Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

And if it's to apply if A1 contains more than 75 characters (not the value,
as per OP), how could it be amended ? Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

And if it's to apply if A1 contains more than 75 characters (not the
value,
as per OP), how could it be amended ? Thanks.

Try changing, Bob's condition:

If .Value 75 Then

to:
If Len(Target) 75 Then



---
Regards,
Norman



"Max" wrote in message
...
And if it's to apply if A1 contains more than 75 characters (not the
value,
as per OP), how could it be amended ? Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Yes, that did it. Thanks, Norman !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

both bits

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .column = 1 Then '<=== changed from .Address =
"$A$1"
If len(.Value) 75 Then '<=== adedd Len(...)
.Font.Size = 8
Else
.Font.Size = 10
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Max" wrote in message
...
And if it's to apply if A1 contains more than 75 characters (not the
value,
as per OP), how could it be amended ? Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Bob, thanks ! That works nicely.
Got the entire sheet bit from Norman's response <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Max,

To apply Bob's code for column A, try this minor adaptation::

'<<========================
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng1 As Range, rng2 As Range
Dim rCell As Range

Set rng1 = Me.Columns(1)

Set rng2 = Intersect(Target, rng1)

On Error GoTo ws_exit:
Application.EnableEvents = False

If Not Intersect(Target, rng1) Is Nothing Then
For Each rCell In rng2.Cells
With rCell
If .Value 75 Then
.Font.Size = 8
Else
.Font.Size = 10
End If
End With
Next
End If

ws_exit:
Application.EnableEvents = True
End Sub
'<<========================


To extend use to the entire sheet, change:

Set rng1 = Me.Columns(1)

to:

Set rng1 = Me.Cells


---
Regards,
Norman



"Max" wrote in message
...
If I want it to apply for the entire col A, or the entire sheet,
how could your sub be amended ?
Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks, Norman !
It works fine ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--