Macro thing?

[Jerry Kinder]

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]

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]

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]

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(xlCellTypeFormulas, 1)

--

HTH

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

 

[Jerry Kinder]

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]

So did you try my suggestion?

--

HTH

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

 

[Jerry Kinder]

I made the change but must have not got it right because the changes
still