Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior Color Macro
On line six below, the interior color should be set to 36 only when:
1. Cell B38 is selected (by the "GoTo" macro) 2. The worksheet is active. So, when another cell is selected, or the user leaves the worksheet, the cell returns to its original interior color. How should this code be modified? Sub GoToTFMChangeRequestWorksheetComptroller() Application.ScreenUpdating = False Sheets("TFM Change Request Worksheet").Select Application.Goto Reference:=Range("A38"), Scroll:=True Application.Goto Reference:=Range("B38"), Scroll:=False 'Places curser off screen Selection.Interior.ColorIndex = 36 ActiveWindow.Zoom = 100 Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior Color Macro
You could put this code behind the worksheet affected, the event trigger
will test if cell B38 is selected and change the color, else reset it......... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$38" Then Range("B38").Interior.ColorIndex = 36 Else Range("B38").Interior.ColorIndex = 0 End If End Sub -- Cheers Nigel "Phil H" wrote in message ... On line six below, the interior color should be set to 36 only when: 1. Cell B38 is selected (by the "GoTo" macro) 2. The worksheet is active. So, when another cell is selected, or the user leaves the worksheet, the cell returns to its original interior color. How should this code be modified? Sub GoToTFMChangeRequestWorksheetComptroller() Application.ScreenUpdating = False Sheets("TFM Change Request Worksheet").Select Application.Goto Reference:=Range("A38"), Scroll:=True Application.Goto Reference:=Range("B38"), Scroll:=False 'Places curser off screen Selection.Interior.ColorIndex = 36 ActiveWindow.Zoom = 100 Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior Color Macro
Hi Nigel,
Your code works exactly as intended. Thanks. Can we take this a step further? In the worksheet there are now five merged cell ranges that need to be treated the same way: A3:M3; A23:M23; A38:M38 (instead of the single cell B38); A49:M49; and A55:M55. What would the code be? Thanks, Phil "Nigel" wrote: You could put this code behind the worksheet affected, the event trigger will test if cell B38 is selected and change the color, else reset it......... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$38" Then Range("B38").Interior.ColorIndex = 36 Else Range("B38").Interior.ColorIndex = 0 End If End Sub -- Cheers Nigel "Phil H" wrote in message ... On line six below, the interior color should be set to 36 only when: 1. Cell B38 is selected (by the "GoTo" macro) 2. The worksheet is active. So, when another cell is selected, or the user leaves the worksheet, the cell returns to its original interior color. How should this code be modified? Sub GoToTFMChangeRequestWorksheetComptroller() Application.ScreenUpdating = False Sheets("TFM Change Request Worksheet").Select Application.Goto Reference:=Range("A38"), Scroll:=True Application.Goto Reference:=Range("B38"), Scroll:=False 'Places curser off screen Selection.Interior.ColorIndex = 36 ActiveWindow.Zoom = 100 Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior Color Macro
'-----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "A3,A23,A38,A49,A55" Dim cell As Range If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Interior.ColorIndex = 36 End With Else For Each cell In Me.Range(WS_RANGE) cell.Interior.ColorIndex = xlColorIndexNone Next cell End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Phil H" wrote in message ... Hi Nigel, Your code works exactly as intended. Thanks. Can we take this a step further? In the worksheet there are now five merged cell ranges that need to be treated the same way: A3:M3; A23:M23; A38:M38 (instead of the single cell B38); A49:M49; and A55:M55. What would the code be? Thanks, Phil "Nigel" wrote: You could put this code behind the worksheet affected, the event trigger will test if cell B38 is selected and change the color, else reset it......... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$38" Then Range("B38").Interior.ColorIndex = 36 Else Range("B38").Interior.ColorIndex = 0 End If End Sub -- Cheers Nigel "Phil H" wrote in message ... On line six below, the interior color should be set to 36 only when: 1. Cell B38 is selected (by the "GoTo" macro) 2. The worksheet is active. So, when another cell is selected, or the user leaves the worksheet, the cell returns to its original interior color. How should this code be modified? Sub GoToTFMChangeRequestWorksheetComptroller() Application.ScreenUpdating = False Sheets("TFM Change Request Worksheet").Select Application.Goto Reference:=Range("A38"), Scroll:=True Application.Goto Reference:=Range("B38"), Scroll:=False 'Places curser off screen Selection.Interior.ColorIndex = 36 ActiveWindow.Zoom = 100 Application.ScreenUpdating = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior Color Macro
Thanks Bob, it works perfectly! Appriciate your time and expertise.
"Bob Phillips" wrote: '----------------------------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "A3,A23,A38,A49,A55" Dim cell As Range If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Interior.ColorIndex = 36 End With Else For Each cell In Me.Range(WS_RANGE) cell.Interior.ColorIndex = xlColorIndexNone Next cell End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Phil H" wrote in message ... Hi Nigel, Your code works exactly as intended. Thanks. Can we take this a step further? In the worksheet there are now five merged cell ranges that need to be treated the same way: A3:M3; A23:M23; A38:M38 (instead of the single cell B38); A49:M49; and A55:M55. What would the code be? Thanks, Phil "Nigel" wrote: You could put this code behind the worksheet affected, the event trigger will test if cell B38 is selected and change the color, else reset it......... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$B$38" Then Range("B38").Interior.ColorIndex = 36 Else Range("B38").Interior.ColorIndex = 0 End If End Sub -- Cheers Nigel "Phil H" wrote in message ... On line six below, the interior color should be set to 36 only when: 1. Cell B38 is selected (by the "GoTo" macro) 2. The worksheet is active. So, when another cell is selected, or the user leaves the worksheet, the cell returns to its original interior color. How should this code be modified? Sub GoToTFMChangeRequestWorksheetComptroller() Application.ScreenUpdating = False Sheets("TFM Change Request Worksheet").Select Application.Goto Reference:=Range("A38"), Scroll:=True Application.Goto Reference:=Range("B38"), Scroll:=False 'Places curser off screen Selection.Interior.ColorIndex = 36 ActiveWindow.Zoom = 100 Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to set cell interior color? | Excel Discussion (Misc queries) | |||
No Interior Color Macro | Excel Discussion (Misc queries) | |||
Sum all cells with interior color... | Excel Programming | |||
Print without Interior Color | Excel Discussion (Misc queries) | |||
Passing Back Color to Interior Color | Excel Programming |