Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi - hopefully this is something simple:
I have a block fo data - F5:BM265. what i am trying to do is create a macro that checks row 5 at the top of each column (which is a month) and then if the month is june. make the column a certain format (bold and larger font). Yes conditional formating can do it - but if I want to change the special formatting i would ahve to go change 60 columns... oh and yes the dates would changes so it wouldnt be just 5 columns would have to be all 60. got no idea where to start on this can anyone help me?! thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Glimmer23,
Try: '=================== Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = Thisworkbook.Sheets("Sheet1") '<<======= CHANGE Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then 'June With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub ''<<'=================== --- Regards, Norman wrote in message ups.com... Hi - hopefully this is something simple: I have a block fo data - F5:BM265. what i am trying to do is create a macro that checks row 5 at the top of each column (which is a month) and then if the month is june. make the column a certain format (bold and larger font). Yes conditional formating can do it - but if I want to change the special formatting i would ahve to go change 60 columns... oh and yes the dates would changes so it wouldnt be just 5 columns would have to be all 60. got no idea where to start on this can anyone help me?! thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
does not quite work - did i go wrong? - I get the error, run-time error
9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Glimmer, does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. At a guess, this is because, you have not put the code into the workbook which houses the "fp12m" sheet. Solution, change: Set sh = ThisWorkbook.Sheets("fp12m") to: Set sh = ActiveWorkbook.Sheets("fp12m") If this is not the problem, then check that the fp12m worksheet exists and that its name is spelled correctly - also look for leading or trailing spaces! --- Regards, Norman wrote in message oups.com... does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi,
Probably failed because of the format of the cells in row 5. The following, a variation which is a bit longer and less elegant, may work. For months other than "June", change accordingly. Sub Test() Dim Col 'reset full range (exc. column header) With Range("F6:BM265").Font .Size = 10 .Bold = False End With 'using column numbers e.g. A=1, B=2 etc 'F=6 BM=65 For Col = 6 To 65 If Cells(5, Col) = "June" Then 'highlight (exc. column header) With Range(Cells(6, Col), Cells(265, Col)).Font .Size = 12 .Bold = True End With End If Next Col End Sub Don wrote in message oups.com... does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Gliimer,
To add, Solution, change: Set sh = ThisWorkbook.Sheets("fp12m") to: Set sh = ActiveWorkbook.Sheets("fp12m") Or, alternatively, leave the code unchanged and move it to a standard module in the workbook which houses the "fp12m" worksheet. As a general rule of thumb, if code is to be used exclusively with a given workbook, store the code with the workbook. --- Regards, Norman "Norman Jones" wrote in message ... Hi Glimmer, does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. At a guess, this is because, you have not put the code into the workbook which houses the "fp12m" sheet. Solution, change: Set sh = ThisWorkbook.Sheets("fp12m") to: Set sh = ActiveWorkbook.Sheets("fp12m") If this is not the problem, then check that the fp12m worksheet exists and that its name is spelled correctly - also look for leading or trailing spaces! --- Regards, Norman wrote in message oups.com... does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Don,
Probably failed because of the format of the cells in row 5. No. This would not give a run-time 9 'Subscript out of range' message. This message indicates that the code is unable to locate the specified worksheet: either because the worksheet and code are in different workbooks, the worksheet's name is mis-spelled or the sheet does not exist. --- Regards, Norman "Don Lloyd" wrote in message ... Hi, Probably failed because of the format of the cells in row 5. The following, a variation which is a bit longer and less elegant, may work. For months other than "June", change accordingly. Sub Test() Dim Col 'reset full range (exc. column header) With Range("F6:BM265").Font .Size = 10 .Bold = False End With 'using column numbers e.g. A=1, B=2 etc 'F=6 BM=65 For Col = 6 To 65 If Cells(5, Col) = "June" Then 'highlight (exc. column header) With Range(Cells(6, Col), Cells(265, Col)).Font .Size = 12 .Bold = True End With End If Next Col End Sub Don wrote in message oups.com... does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
ah yes - i messed up there hehe fixed that now though... was using the
macro sheet name rather than actual sheet name (been long day) anyways stepping into the code now getting a "type mismatch" (error 13) error on: With rCell.Resize(sh.UsedRange.Rows.Count).Font could this be because the data is not continous down the column? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Thanks Norman,
I got up early 'cause I couldn't sleep so I thought I'd interfere somewhere to while away the time ! Succeeded :-) Don "Norman Jones" wrote in message ... Hi Don, Probably failed because of the format of the cells in row 5. No. This would not give a run-time 9 'Subscript out of range' message. This message indicates that the code is unable to locate the specified worksheet: either because the worksheet and code are in different workbooks, the worksheet's name is mis-spelled or the sheet does not exist. --- Regards, Norman "Don Lloyd" wrote in message ... Hi, Probably failed because of the format of the cells in row 5. The following, a variation which is a bit longer and less elegant, may work. For months other than "June", change accordingly. Sub Test() Dim Col 'reset full range (exc. column header) With Range("F6:BM265").Font .Size = 10 .Bold = False End With 'using column numbers e.g. A=1, B=2 etc 'F=6 BM=65 For Col = 6 To 65 If Cells(5, Col) = "June" Then 'highlight (exc. column header) With Range(Cells(6, Col), Cells(265, Col)).Font .Size = 12 .Bold = True End With End If Next Col End Sub Don wrote in message oups.com... does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
got it to work like this:
Public Sub FormatJune() Dim Col For Col = 6 To 65 If Month(Cells(5, Col)) = 6 Then With Range(Cells(5, Col), Cells(265, Col)).Font .Size = 12 .Bold = True End With End If Next Col End Sub Thanks for that - fi there is a better way to do it still would love to hear it. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
excpet it doesnt reset ti back to what it was... which means if the
months change it leaves it as bold... i knwo you had a removal section however that makes it all unbolb where some rows are bolded already... |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
far out my apologies for all the typos - not thinkings straight atm...
right was thinking we can grab the format default format off another sheet which does not require the bolding if thats possible?! ie copy format of range on 1 sheet apply it to same range on sheet 2 then bold sheet 2 columns where month()=6 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Glimmer,
anyways stepping into the code now getting a "type mismatch" (error 13) error on: Are you surethat the error is not in fact occurring on the preceding line? If this is the case, it would suggest that the entries in row 5 are not recognised as dates. So if the entries in row 5 are text value like "Jun" or "June", then change the line: If Month(rCell.Value) = 6 Then to: If UCase(Left(rCell.Value, 3)) = "JUN" Then If this does not resolve your problem. post back giving an example of the row 5 header text. --- Regards, Norman wrote in message oups.com... ah yes - i messed up there hehe fixed that now though... was using the macro sheet name rather than actual sheet name (been long day) anyways stepping into the code now getting a "type mismatch" (error 13) error on: With rCell.Resize(sh.UsedRange.Rows.Count).Font could this be because the data is not continous down the column? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Don,
No problem! If the OP's problem related to the non-recognition of date values in the row 5 headers, I would have expected a run-time 13 error - exactly as now reported by the OP. In advance of confirmation by the OP, it would appear that both problems prevailed: namely the recognition of the worksheet (run-time error 9) and the date recognition (run-time error 13). --- Regards, Norman "Don Lloyd" wrote in message ... Thanks Norman, I got up early 'cause I couldn't sleep so I thought I'd interfere somewhere to while away the time ! Succeeded :-) Don "Norman Jones" wrote in message ... Hi Don, Probably failed because of the format of the cells in row 5. No. This would not give a run-time 9 'Subscript out of range' message. This message indicates that the code is unable to locate the specified worksheet: either because the worksheet and code are in different workbooks, the worksheet's name is mis-spelled or the sheet does not exist. --- Regards, Norman "Don Lloyd" wrote in message ... Hi, Probably failed because of the format of the cells in row 5. The following, a variation which is a bit longer and less elegant, may work. For months other than "June", change accordingly. Sub Test() Dim Col 'reset full range (exc. column header) With Range("F6:BM265").Font .Size = 10 .Bold = False End With 'using column numbers e.g. A=1, B=2 etc 'F=6 BM=65 For Col = 6 To 65 If Cells(5, Col) = "June" Then 'highlight (exc. column header) With Range(Cells(6, Col), Cells(265, Col)).Font .Size = 12 .Bold = True End With End If Next Col End Sub Don wrote in message oups.com... does not quite work - did i go wrong? - I get the error, run-time error 9, subscript out of range. Public Sub FormatJune() Dim sh As Worksheet Dim rng As Range Dim rCell As Range Set sh = ThisWorkbook.Sheets("fp12m") Set rng = sh.Range("B5:BM5") For Each rCell In rng.Cells If Month(rCell.Value) = 6 Then With rCell.Resize(sh.UsedRange.Rows.Count).Font .Bold = True .Size = 14 End With End If Next rCell End Sub |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Glimmer,
The suggested code operates on demand, i.e. when you run it. If you want dynamic response, and do not want to use cinditional formatting, then you need to use Event code. So, in the code module behind the worksheet (right-click the sheet's tab | View Code) paste the following event procedu '============================== Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("B5"). _ Resize(1, 60), Target) Is Nothing Then If IsDate(Target) Then If Month(Target.Value) = 6 Then With Target.Resize(260) .Font.Bold = True .Font.Size = 12 End With Else With Target.Resize(260) .Font.Bold = False .Font.Size = 10 End With End If Else With Target.Resize(260) .Font.Bold = False .Font.Size = 10 End With End If End If End Sub '<<============================== --- Regards, Norman wrote in message oups.com... excpet it doesnt reset ti back to what it was... which means if the months change it leaves it as bold... i knwo you had a removal section however that makes it all unbolb where some rows are bolded already... |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
ok my final solution---
probably can be improved and if you knwo it could be please feel free to tell me :) right its broken into three macros. 1 which detects the date change on the data sheet: Private Sub Worksheet_Change(ByVal target As Range) Application.ScreenUpdating = False Dim rng3 As Range Set rng3 = Range("B41") If Not Intersect(target, rng3) Is Nothing Then Run "FormatJuneStart" Run "fp12m.FormatJuneEnd" End If Application.ScreenUpdating = True End Sub 1 in the modules section cause i recorded it from excel - this is the one which copies old format and paste specaisl the format over the new range. Public Sub FormatJuneStart() Application.ScreenUpdating = False Sheets("Fin Proj").Select Range("F5:BM265").Select Selection.Copy Sheets("12 Mths Fin Proj").Select Range("F5:BM265").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("BL247").Select Range("F169:BM169").Select Range("F6").Activate Selection.Interior.ColorIndex = xlNone Application.ScreenUpdating = True End Sub and finally the one which highlights the columns which are june Public Sub FormatJuneEnd() Application.ScreenUpdating = False Dim Col For Col = 6 To 65 If Month(Cells(5, Col)) = 6 Then With Range(Cells(5, Col), Cells(265, Col)).Font .Size = 10 .Bold = True End With End If Next Col Application.ScreenUpdating = True End Sub :) yay thank god thats done LOL thanks for your help guys |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formationg Macro
Hi Glimmer,
Did you try the posted Event macro? --- Regards, Norman wrote in message oups.com... ok my final solution--- probably can be improved and if you knwo it could be please feel free to tell me :) right its broken into three macros. 1 which detects the date change on the data sheet: Private Sub Worksheet_Change(ByVal target As Range) Application.ScreenUpdating = False Dim rng3 As Range Set rng3 = Range("B41") If Not Intersect(target, rng3) Is Nothing Then Run "FormatJuneStart" Run "fp12m.FormatJuneEnd" End If Application.ScreenUpdating = True End Sub 1 in the modules section cause i recorded it from excel - this is the one which copies old format and paste specaisl the format over the new range. Public Sub FormatJuneStart() Application.ScreenUpdating = False Sheets("Fin Proj").Select Range("F5:BM265").Select Selection.Copy Sheets("12 Mths Fin Proj").Select Range("F5:BM265").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("BL247").Select Range("F169:BM169").Select Range("F6").Activate Selection.Interior.ColorIndex = xlNone Application.ScreenUpdating = True End Sub and finally the one which highlights the columns which are june Public Sub FormatJuneEnd() Application.ScreenUpdating = False Dim Col For Col = 6 To 65 If Month(Cells(5, Col)) = 6 Then With Range(Cells(5, Col), Cells(265, Col)).Font .Size = 10 .Bold = True End With End If Next Col Application.ScreenUpdating = True End Sub :) yay thank god thats done LOL thanks for your help guys |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Macro | Excel Discussion (Misc queries) | |||
[B]Conditional Macro?[/B] | Excel Discussion (Misc queries) | |||
open cell formationg dialogue from macro | Excel Programming | |||
conditional macro | Excel Programming | |||
Conditional Macro | Excel Worksheet Functions |