nousername
03-19-2008, 06:54 PM
The code below works great, except for one small issue. Currently the code will change all a row color to yellow and then reset that row back to white, even if the row color was preset to another color value.
I need the row color to change to 6 only when the row color is white and there is text in column A.
How can this be accomplished in using the current code?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This holds the name of the hidden defined name that
'we use to store the old target rows address in
Const szRCName As String = "rgnRC"
Dim rRng As Excel.Range
Dim szOldTarget As String
Dim vArrCellTypes As Variant
Dim vCell As Variant
'Store the special cells types that we use in an array
vArrCellTypes = Array(xlCellTypeConstants, xlCellTypeFormulas, xlCellTypeAllValidation, xlCellTypeBlanks)
On Error Resume Next
'Create a valid row address by cutting the extra's from
'the named ranges RefersTo value
szOldTarget = Replace$(Names(szRCName).RefersTo, "=", "")
szOldTarget = Replace$(szOldTarget, """", "")
Application.EnableEvents = False
Application.ScreenUpdating = False
'Reset color of the old target row:
With Range(szOldTarget)
.Interior.ColorIndex = 0
.Font.Bold = False
End With
'Range to check is the entire row
'If bRw Then
Set rRng = Range("A" & Target.Row & ":" & "F" & Target.Row)
'Else
' Set rRng = Range(Target.EntireColumn.Address)
' End If
'=======================================================
'Loop through the SpecialCell types array:
For Each vCell In vArrCellTypes
'Format the cells we find:
With rRng.SpecialCells(CLng(vCell))
.Interior.ColorIndex = 6
.Font.Bold = True
End With
Next vCell
'=======================================================
'Update our defined name with the row address:
'The defined name is set to hidden so it cannot be viewed
'in the Names dialog, change to suit.
'If bRw Then
Names.Add szRCName, Target.EntireRow.Address, False
'Else
' Names.Add szRCName, Target.EntireColumn.Address, False
' End If
Application.EnableEvents = True
Application.ScreenUpdating = True
'Explicitly clear memory
Set rRng = Nothing
End Sub
I need the row color to change to 6 only when the row color is white and there is text in column A.
How can this be accomplished in using the current code?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This holds the name of the hidden defined name that
'we use to store the old target rows address in
Const szRCName As String = "rgnRC"
Dim rRng As Excel.Range
Dim szOldTarget As String
Dim vArrCellTypes As Variant
Dim vCell As Variant
'Store the special cells types that we use in an array
vArrCellTypes = Array(xlCellTypeConstants, xlCellTypeFormulas, xlCellTypeAllValidation, xlCellTypeBlanks)
On Error Resume Next
'Create a valid row address by cutting the extra's from
'the named ranges RefersTo value
szOldTarget = Replace$(Names(szRCName).RefersTo, "=", "")
szOldTarget = Replace$(szOldTarget, """", "")
Application.EnableEvents = False
Application.ScreenUpdating = False
'Reset color of the old target row:
With Range(szOldTarget)
.Interior.ColorIndex = 0
.Font.Bold = False
End With
'Range to check is the entire row
'If bRw Then
Set rRng = Range("A" & Target.Row & ":" & "F" & Target.Row)
'Else
' Set rRng = Range(Target.EntireColumn.Address)
' End If
'=======================================================
'Loop through the SpecialCell types array:
For Each vCell In vArrCellTypes
'Format the cells we find:
With rRng.SpecialCells(CLng(vCell))
.Interior.ColorIndex = 6
.Font.Bold = True
End With
Next vCell
'=======================================================
'Update our defined name with the row address:
'The defined name is set to hidden so it cannot be viewed
'in the Names dialog, change to suit.
'If bRw Then
Names.Add szRCName, Target.EntireRow.Address, False
'Else
' Names.Add szRCName, Target.EntireColumn.Address, False
' End If
Application.EnableEvents = True
Application.ScreenUpdating = True
'Explicitly clear memory
Set rRng = Nothing
End Sub