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

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

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

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

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

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

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

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?

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

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.

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

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

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?

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

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

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

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