| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
| Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
| Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
| Date macro | Excel Discussion (Misc queries) | |||
Linear Mode
