PDA

View Full Version : Solved: How do you prevent a row color change event?



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

Bob Phillips
03-20-2008, 02:32 AM
That seems ridiculosuly complex code to do what you say it does, and it my testing it did something else, so I think you should post a workbook as I can't wor my way through that.

Simon Lloyd
03-20-2008, 02:42 AM
Try replacing this:
'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 vCellwith


'Loop through the SpecialCell types array:
For Each vCell In vArrCellTypes
If Range("A" & vCell.Row) <> "" And Range("A" & vCell.Row).Interior.ColorIndex = 0 Then
'Format the cells we find:
With rRng.SpecialCells(CLng(vCell))
.Interior.ColorIndex = 6
.Font.Bold = True
End With
End If

Next vCell
I agree with xld, i didn't wade through it or even attempt to test it just tried to give you a quick fix, your code looks very confusing!

nousername
03-20-2008, 05:40 AM
Simon,

I tried the quick fix and it did not work. I have attached a sample workbook with the code and a sample of the rows I am using in the real workbook.

Thanks in advance for looking at this perplexing code!

Nousername

chat163
03-21-2008, 06:08 AM
the issue is happened in :
With Range(szOldTarget)
.Interior.ColorIndex = 0
.Font.Bold = False
.Font.Italic = False
End With

because if you click the new row, it'll reset to white color interior, then will apply the yellow color to new line

nousername
03-21-2008, 06:17 AM
Yes, that is the section of code that I tried to wrap something around to catch if the color is something other than yellow. The code I tried never reset the row color back to white when it was yellow.

Any code suggestions to wrap around this section of code?

chat163
03-21-2008, 06:28 AM
Try.
is it what you want?

chat163
03-21-2008, 06:35 AM
or change
If Application.WorksheetFunction.Index(Range(szOldTarget), 1).Interior.ColorIndex = 6 Then
With Range(szOldTarget)
.Interior.ColorIndex = 0
.Font.Bold = False
.Font.Italic = False
End With
End If
to
If Application.WorksheetFunction.Index(Range(szOldTarget), 1).Interior.ColorIndex <> 6 Then
With Range(szOldTarget)
.Interior.ColorIndex = 0
.Font.Bold = False
.Font.Italic = False
End With
End If

nousername
03-21-2008, 06:44 AM
I cannot believe it the code is working correctly!! MANY THANKS!!!

I have two other small issues with this code:

1. For some reason I can only highlight the option buttons when I add "xlCellTypeBlanks." Is there a way to address this issue?



'Store the special cells types that we use in an array
vArrCellTypes = Array(xlCellTypeConstants, xlCellTypeFormulas, xlCellTypeAllValidation, xlCellTypeBlanks)


2. Is there a way to have the yellow row on a worksheet reset to white when I switch to another worksheet?

chat163
03-21-2008, 06:52 AM
for question 2:
Private Sub Worksheet_Deactivate()
Dim rng As Range
For Each rng In Worksheets("sheet1").UsedRange
If rng.Interior.ColorIndex = 6 Then
rng.Interior.ColorIndex = 0
End If
Next
End Sub

chat163
03-21-2008, 07:01 AM
for question 1:
if you add "xlCellTypeBlanks" it will highlight CDEF column.

you only want to highlight CDE not F column,right?

nousername
03-21-2008, 07:20 AM
Your right xlCellTypeBlanks does highlight columns CDEF. I removed xlCellTypeAllValidation from the line of code and it still works.

Last question. Currently the code uses the right-click method to enable or disable the row highlighter. This method restrict the user from using the right click for any other fucntions. What do you think is the best way to enable or disable the row highlighter?

chat163
03-23-2008, 06:04 AM
Your right xlCellTypeBlanks does highlight columns CDEF. I removed xlCellTypeAllValidation from the line of code and it still works.

Last question. Currently the code uses the right-click method to enable or disable the row highlighter. This method restrict the user from using the right click for any other fucntions. What do you think is the best way to enable or disable the row highlighter?

Maybe below is better.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
*****
End Sub

nousername
03-23-2008, 08:41 AM
Thanks again for the reply. I opened a new post to see if there was a better way to perform this task. I added a contextmenu to my workbook and I am trying to get the code to work to call my Worksheet_SelectionChange code correctly.

I need to create a sample project for the post.

If you have any suggestion please let me know, especially since Excel is not my forte.

Here is the new post:
http://www.vbaexpress.com/forum/showthread.php?p=137220#post137220