conditional formating
 

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

conditional formating
 

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

conditional formating
 

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
--

conditional formating
 

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
--

conditional formating
 

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
--

conditional formating
 

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
--

conditional formating
 

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
--

conditional formating
 

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

conditional formating
 

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

conditional formating
 

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
--

conditional formating
 

Mr.Bob and Mr.Norman thanks
It works perfectly, But one more related question which i asked
yesterday in a different thread. If the cell A1 crossed the 75
character any possibility to move the 76th character to A2 i mean
continuation. For me it is very useful question, to print a cheque leaf
.. I hope both of you will do something on it.
thanks in advance.
nowfal


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

conditional formating
 

Do you want the 76+ characters simply moved to A2, or A1 to be truncated at
75, and 76+ characters simply moved to A2.

I will give you both and you can pick. But what happens on a subsequent
change?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
If Len(.Value) 75 Then
.Font.Size = 8
.Offset(0, 1).Value = Right(.Value, Len(.Value) - 75)
Else
.Font.Size = 10
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub



Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
If Len(.Value) 75 Then
.Font.Size = 8
.Value = Left(.Value, 75)
.Offset(0, 1).Value = Right(.Value, Len(.Value) - 75)
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)


"nowfal" wrote in
message ...

Mr.Bob and Mr.Norman thanks
It works perfectly, But one more related question which i asked
yesterday in a different thread. If the cell A1 crossed the 75
character any possibility to move the 76th character to A2 i mean
continuation. For me it is very useful question, to print a cheque leaf
I hope both of you will do something on it.
thanks in advance.
nowfal


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

conditional formating
 

Mr.Bob,
If that is possible then no need to change the font size
that can be saty as it is on font size 10. So pls write when th
character is more than 75 then the 76th character goes to A2 , i
possible with the word, I mean the character is inbetween the word th
word also should go to the next line. Lot of thanks.
Pls do me this favour too.
with regards
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39553

conditional formating
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
If Len(.Value) 75 Then
iPos = InStrRev(.Value, " ", 76)
If iPos 0 Then
.Offset(0, 1).Value = Right(.Value, Len(.Value) - iPos)
.Value = Left(.Value, iPos)
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

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


"nowfal" wrote in
message ...

Mr.Bob,
If that is possible then no need to change the font size,
that can be saty as it is on font size 10. So pls write when the
character is more than 75 then the 76th character goes to A2 , if
possible with the word, I mean the character is inbetween the word the
word also should go to the next line. Lot of thanks.
Pls do me this favour too.
with 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

conditional formating
 

Hi Mr.Bob
The latest code is working, but 76th character is going
to B2 that is the next cell. For me i need it to go to the next line,
that is A2. so pls try to sort it out.
with 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

conditional formating
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$A$1" Then
If Len(.Value) 75 Then
iPos = InStrRev(.Value, " ", 76)
If iPos 0 Then
.Offset(1,0).Value = Right(.Value, Len(.Value) - iPos)
.Value = Left(.Value, iPos)
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub



--

HTH

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


"nowfal" wrote in
message ...

Hi Mr.Bob
The latest code is working, but 76th character is going
to B2 that is the next cell. For me i need it to go to the next line,
that is A2. so pls try to sort it out.
with 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

conditional formating
 

Hi Mr.Bob,
It is working well,thanks alot. but another proble
comes that , I am getting the data from another source workbook(acces
analys data) this A1 has a paste special formula (from that workbook
after doing this , the formula disappearing. So each time i have to pu
the formula on cell A1.
one more thing if i am deleting the souce line in the other book th
new A2 cell is remaining with the characters . the formula is like thi
=MICR.xls!$N$2. Any solution.
Bob, no hurry at all , whenever you are getting time look on it.
by
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39553

conditional formating
 

If you don't remove the formula, you cannot split the text. If a cell has a
formula, what you see is the result of that formula, that value is not
really in the cell. The code takes that value and overwrites the formula.

You can't have a formula and a value, no can do.

--

HTH

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


"nowfal" wrote in
message ...

Hi Mr.Bob,
It is working well,thanks alot. but another problem
comes that , I am getting the data from another source workbook(access
analys data) this A1 has a paste special formula (from that workbook)
after doing this , the formula disappearing. So each time i have to put
the formula on cell A1.
one more thing if i am deleting the souce line in the other book the
new A2 cell is remaining with the characters . the formula is like this
=MICR.xls!$N$2. Any solution.
Bob, no hurry at all , whenever you are getting time look on it.
by
nowfal


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

conditional formating
 

Hi Bob,
Thanks for that information. Now i will think about a macro
to delete that lines and reinstal the formula after take a print of the
cheque . I think i can manage that way. Thank you once again
with 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

conditional formating
 

Hi Bob,
I have sorted out the problem with a macro. Now fo
another page i am trying with the same code but i wanted the same shee
code in two places, and i try to writed the following way, but i a
failing can u have a look on this, if possible pls correct it.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$B$8" Then
If Len(.Value) 50 Then
iPos = InStrRev(.Value, " ", 51)
If iPos 0 Then
.Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
.Value = Left(.Value, iPos)
End If
With Target
If .Address = "$A$10" Then
If Len(.Value) 50 Then
iPos = InStrRev(.Value, " ", 51)
If iPos 0 Then
.Offset(1, 0).Value = Right(.Value, Len(.Value) - iPos)
.Value = Left(.Value, iPos)
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

thanks and regards
nowfa

--
nowfa
-----------------------------------------------------------------------
nowfal's Profile: http://www.excelforum.com/member.php...fo&userid=1000
View this thread: http://www.excelforum.com/showthread.php?threadid=39553

conditional formating
 

Anybody there to assist, i am getting error message.
thanks
by
nowfal


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