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

[nousername]

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]

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]

Try replacing this:
[VBA]'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[/VBA]with

[VBA]
'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
[/VBA]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]

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]

the issue is happened in :
[VBA] With Range(szOldTarget)
.Interior.ColorIndex = 0
.Font.Bold = False
.Font.Italic = False
End With[/VBA]

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

[nousername]

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]

Try.
is it what you want?

[chat163]

or change
[VBA] 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[/VBA]
to
[VBA] 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[/VBA]

[nousername]

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]

for question 2:
[VBA]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[/VBA]

[chat163]

for question 1:
if you add "xlCellTypeBlanks" it will highlight CDEF column.

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

[nousername]

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]

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.
[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
*****
End Sub
[/VBA]

[nousername]

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/show...220#post137220