conditional formating

[nowfal]

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.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

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

[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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
>
>

[Norman Jones]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
> --
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
>
>

[Max]

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

[Max]

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

[Max]

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

[nowfal]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[nowfal]

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]

Hi Bob,
I have sorted out the problem with a macro. Now for another page i am trying with the same code but i wanted the same sheet code in two places, and i try to writed the following way, but i am 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
nowfal

[nowfal]

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