highlighting question beyond me

[pontiff]

Last one for today, its just this stuff is so addictive!

I would like to be able to click on a cell eg A1 or A2 or A3 etc up to A19 , have the code look at some cells further down in that column ( eg A20:A30), and highlight any of those cells (A20:A30) which contain the value 0 ( fill them yellow maybe).

Then remove the highlighting when i click on another cell.

Hope I've managed to explain this.

Cheers,
Pontiff

 

[Rick Rothstein]

You could use this event code to do what you asked for...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim X As Long
  If Intersect(Target(1), Range("A1:A19")) Is Nothing Then
    Range("A20:A30").Interior.ColorIndex = xlColorIndexNone
  Else
    For X = 20 To 30
      If Cells(X, "A").Value = 0 Then Cells(X, "A").Interior.ColorIndex = 6
    Next
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[pontiff]

You could use this event code to do what you asked for...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim X As Long
  If Intersect(Target(1), Range("A1:A19")) Is Nothing Then
    Range("A20:A30").Interior.ColorIndex = xlColorIndexNone
  Else
    For X = 20 To 30
      If Cells(X, "A").Value = 0 Then Cells(X, "A").Interior.ColorIndex = 6
    Next
  End If
End Sub

Wow! If I want to do this for columns A to N for example, ( always looking in cells 20 to 30 in whichever column cell I've selected, would I need a separate piece of code for each column?

 

[Rick Rothstein]

Wow! If I want to do this for columns A to N for example, ( always looking in cells 20 to 30 in whichever column cell I've selected, would I need a separate piece of code for each column?

No, just replace the code I gave you earlier with this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim X As Long
  Range("A20:N30").Interior.ColorIndex = xlColorIndexNone
  If Not Intersect(Target(1), Range("A1:N19")) Is Nothing Then
    For X = 20 To 30
      If Cells(X, Target.Column).Value = 0 Then Cells(X, Target.Column).Interior.ColorIndex = 6
    Next
  End If
End Sub

For future questions you might ask... it is almost always a bad idea to "simplify" your question for us, doing so will just tend to get you a great answer for a question you do not really have and that you cannot make use of for your actual situation.

 

[pontiff]

That's great thanks!!!

And next week folks, it's drop down table fun!

 

[pontiff]

No, just replace the code I gave you earlier with this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim X As Long
  Range("A20:N30").Interior.ColorIndex = xlColorIndexNone
  If Not Intersect(Target(1), Range("A1:N19")) Is Nothing Then
    For X = 20 To 30
      If Cells(X, Target.Column).Value = 0 Then Cells(X, Target.Column).Interior.ColorIndex = 6
    Next
  End If
End Sub

For future questions you might ask... it is almost always a bad idea to "simplify" your question for us, doing so will just tend to get you a great answer for a question you do not really have and that you cannot make use of for your actual situation.

Ok here goes! Using this code when I click on a cell between A1:N19, the cells in that column from row 20 to 30 highlight themselves if they contain zero. It works great but here comes the next step!

This all now starts at row B because I have added a list of names in A20 to A30. What I would like to now do is not only highlight the relevant cells containing zero but then look to see which names they correspond to in column A and copy and paste the names into sheet 2, column A.
When I then click on another cell, the selected names copy and paste into sheet 2, column B, and so on.
I guess I would also need some way to reset so I can go back to sheet2 column A for another set of selections.

This I believe would be last piece of the puzzle, the rest I can do myself ( he hopes!)

Thank you for everything you have done,
Phil