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 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 |
#6
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? |
#7
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? |
#8
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 |
#9
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 |
#10
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 |
#11
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. |
#12
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... |
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 |