ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Macro thing? (https://www.excelbanter.com/excel-worksheet-functions/51245-macro-thing.html)

Jerry Kinder
Macro thing?
 
Hi,
New to macro use. I installed a macro to produce cell formatting when a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any cell in the
spread sheet looses its color format with the use of "delete" or any change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to ( B5:b300) ??

Thanks,
Jerry

Bob Phillips
Macro thing?
 
Show us the macro and we might be able to help.

Are you using worksheet event code, or running the macro manually.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
New to macro use. I installed a macro to produce cell formatting when a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any cell in the
spread sheet looses its color format with the use of "delete" or any
change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to ( B5:b300) ??

Thanks,
Jerry

Jerry Kinder
Macro thing?
 
Hi, sorry. This macro is in the work sheet, not the entire work book, where
i use it.
I want to restrict it to just the range ( B4:b300) i want it to work on so
the reat of the work sheet will not be affected by it.
Thanks, Jerry

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Case "Phoenix"
Cell.Interior.ColorIndex = 47
Cell.Font.Bold = False
Case "Phx-MGn"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "Serama"
Cell.Interior.ColorIndex = 38
Cell.Font.Bold = False
Case "MG"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "MG/Serama"
Cell.Interior.ColorIndex = 44
Cell.Font.Bold = False
Case "Phx/Serama"
Cell.Interior.ColorIndex = 8
Cell.Font.Bold = False
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

"Bob Phillips" wrote in message
...
Show us the macro and we might be able to help.

Are you using worksheet event code, or running the macro manually.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
New to macro use. I installed a macro to produce cell formatting when a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any cell in
the
spread sheet looses its color format with the use of "delete" or any
change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to ( B5:b300) ??

Thanks,
Jerry

Bob Phillips
Macro thing?
 
Jerry,

Do you want to restrict it to just trapping a change in B4:B300, or just
work on formulas in the range B4:B300 if any cell changes?

If the latter, try changing

Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

to

Set Rng1 = ActiveSheet.Range("B4:B300").Special(xlCellTypeFor mulas, 1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi, sorry. This macro is in the work sheet, not the entire work book,
where
i use it.
I want to restrict it to just the range ( B4:b300) i want it to work on so
the reat of the work sheet will not be affected by it.
Thanks, Jerry

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Case "Phoenix"
Cell.Interior.ColorIndex = 47
Cell.Font.Bold = False
Case "Phx-MGn"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "Serama"
Cell.Interior.ColorIndex = 38
Cell.Font.Bold = False
Case "MG"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "MG/Serama"
Cell.Interior.ColorIndex = 44
Cell.Font.Bold = False
Case "Phx/Serama"
Cell.Interior.ColorIndex = 8
Cell.Font.Bold = False
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

"Bob Phillips" wrote in message
...
Show us the macro and we might be able to help.

Are you using worksheet event code, or running the macro manually.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
New to macro use. I installed a macro to produce cell formatting when
a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any cell in
the
spread sheet looses its color format with the use of "delete" or any
change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to ( B5:b300)
??

Thanks,
Jerry

Jerry Kinder
Macro thing?
 
Hi,
I want the macro to change the cell color only in cells in Col "B4:B300".
I made the change but must have not got it right because the changes still
take place in any cell in the sheet.
Thanks,
Jerry


"Bob Phillips" wrote in message
...
Jerry,

Do you want to restrict it to just trapping a change in B4:B300, or just
work on formulas in the range B4:B300 if any cell changes?

If the latter, try changing

Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

to

Set Rng1 = ActiveSheet.Range("B4:B300").Special(xlCellTypeFor mulas, 1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi, sorry. This macro is in the work sheet, not the entire work book,
where
i use it.
I want to restrict it to just the range ( B4:b300) i want it to work on
so
the reat of the work sheet will not be affected by it.
Thanks, Jerry

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Case "Phoenix"
Cell.Interior.ColorIndex = 47
Cell.Font.Bold = False
Case "Phx-MGn"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "Serama"
Cell.Interior.ColorIndex = 38
Cell.Font.Bold = False
Case "MG"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "MG/Serama"
Cell.Interior.ColorIndex = 44
Cell.Font.Bold = False
Case "Phx/Serama"
Cell.Interior.ColorIndex = 8
Cell.Font.Bold = False
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

"Bob Phillips" wrote in message
...
Show us the macro and we might be able to help.

Are you using worksheet event code, or running the macro manually.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
New to macro use. I installed a macro to produce cell formatting
when
a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any cell
in
the
spread sheet looses its color format with the use of "delete" or any
change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to (
B5:b300)
??

Thanks,
Jerry

Bob Phillips
Macro thing?
 
So did you try my suggestion?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
I want the macro to change the cell color only in cells in Col "B4:B300".
I made the change but must have not got it right because the changes still
take place in any cell in the sheet.
Thanks,
Jerry


"Bob Phillips" wrote in message
...
Jerry,

Do you want to restrict it to just trapping a change in B4:B300, or just
work on formulas in the range B4:B300 if any cell changes?

If the latter, try changing

Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

to

Set Rng1 = ActiveSheet.Range("B4:B300").Special(xlCellTypeFor mulas,
1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi, sorry. This macro is in the work sheet, not the entire work book,
where
i use it.
I want to restrict it to just the range ( B4:b300) i want it to work
on
so
the reat of the work sheet will not be affected by it.
Thanks, Jerry

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Case "Phoenix"
Cell.Interior.ColorIndex = 47
Cell.Font.Bold = False
Case "Phx-MGn"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "Serama"
Cell.Interior.ColorIndex = 38
Cell.Font.Bold = False
Case "MG"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "MG/Serama"
Cell.Interior.ColorIndex = 44
Cell.Font.Bold = False
Case "Phx/Serama"
Cell.Interior.ColorIndex = 8
Cell.Font.Bold = False
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

"Bob Phillips" wrote in message
...
Show us the macro and we might be able to help.

Are you using worksheet event code, or running the macro manually.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
New to macro use. I installed a macro to produce cell formatting
when
a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any cell
in
the
spread sheet looses its color format with the use of "delete" or
any
change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to (
B5:b300)
??

Thanks,
Jerry

Jerry Kinder
Macro thing?
 
I made the change but must have not got it right because the changes
still
take place in any cell in the sheet.

"Bob Phillips" wrote in message
...
So did you try my suggestion?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
I want the macro to change the cell color only in cells in Col
"B4:B300".
I made the change but must have not got it right because the changes
still
take place in any cell in the sheet.
Thanks,
Jerry


"Bob Phillips" wrote in message
...
Jerry,

Do you want to restrict it to just trapping a change in B4:B300, or
just
work on formulas in the range B4:B300 if any cell changes?

If the latter, try changing

Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)

to

Set Rng1 =
ActiveSheet.Range("B4:B300").Special(xlCellTypeFor mulas,
1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi, sorry. This macro is in the work sheet, not the entire work
book,
where
i use it.
I want to restrict it to just the range ( B4:b300) i want it to work
on
so
the reat of the work sheet will not be affected by it.
Thanks, Jerry

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Case "Phoenix"
Cell.Interior.ColorIndex = 47
Cell.Font.Bold = False
Case "Phx-MGn"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "Serama"
Cell.Interior.ColorIndex = 38
Cell.Font.Bold = False
Case "MG"
Cell.Interior.ColorIndex = 50
Cell.Font.Bold = False
Case "MG/Serama"
Cell.Interior.ColorIndex = 44
Cell.Font.Bold = False
Case "Phx/Serama"
Cell.Interior.ColorIndex = 8
Cell.Font.Bold = False
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

"Bob Phillips" wrote in message
...
Show us the macro and we might be able to help.

Are you using worksheet event code, or running the macro manually.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry Kinder" wrote in message
...
Hi,
New to macro use. I installed a macro to produce cell
formatting
when
a
specific word is entered (about 10 words) in a cell.

I think I need to specify the column for this because now any
cell
in
the
spread sheet looses its color format with the use of "delete" or
any
change
to that cell so other formatting is not stable now.

How do I tell the macro what range to restrict its self to (
B5:b300)
??

Thanks,
Jerry


All times are GMT +1. The time now is 09:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com