Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formating
Thanks, Norman !
It works fine .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional formating
Yes, that did it. Thanks, Norman !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating help. | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATING!!!!! | Excel Discussion (Misc queries) | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) |