Solved how to add multiple VBA codes in one sheet code view

March 5, 2016 at 19:13:36
Specs: Windows 7
sir, I want to highlight excel cell by selecting different cell. for example:
highlight cell b2 when cell c2 is selected and highlight cell b2 when cell d2 is selected and highlight cell b2 when cell e2 is selected and like so on......

the following code is working for highlighting the left/right cell but I want to have a multiple conditions.

Private Sub Worksheet_SelectionChange(ByVal target As Range)
On Error Resume Next
'Get address to reset highlighting
oldTarget = Range("C2")
'Reset highlighting
Range(oldTarget).Interior.ColorIndex = xlNone
'Highlight cell -1 columns to the right
target.Offset(0, -1).Interior.ColorIndex = 6
'Store highlighted cell address for use next time
Range("C2") = target.Offset(0, -1).Address
End Sub

message edited by Azan


See More: how to add multiple VBA codes in one sheet code view

Report •


#1
March 6, 2016 at 06:40:24
It is not clear to me what you are trying to accomplish.

In your post you say "highlight cell b2" 3 times, for 3 separate conditions. Is B2 the only cell that you want to highlight?

Then you specify 3 conditions (the selection of C2, D2 & E2) and end with "and like so on".

What specifically does "and like so on" mean? Are you saying that you want to highlight B2 whenever any cell in Row 2 is selected?

If so, what do you want to happen when a cell outside of Row 2 is selected? Should the highlighting of B2 be turned off?

Please supply some more details so that we have a clearer understanding of what you are trying to accomplish.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 6, 2016 at 10:09:46
Dear Sir,
With well wishes of the day,
Thank you so much for your response, you are right,
I have a wide sheet. In which the column B is the questioner column and it has 27 Questions (B1:B27).
In the right side columns C, D, E, F, G…up to …. AF are the people names, and they are should answer each question which are in the column B.
So in this case I want to highlight the questions which are in the Column B during selecting (answered) cells in C, D, E, F, G…..AF columns.
For example when I select the C7 or D7 or E7 or F7 or G7………………AF7 cell- the B7 cell should be highlight (highlight B7 cell when C7 or D7 or E7 or F7 or G7…………… AF7 cell is selected)
And
Highlight B8 Cell when C8 or D8 or E8 or F8 or G8…….AF8 cell is selected
When I am changing the Row in right side columns the Highlighting in Column B should be change accordingly.
For Example
If I select C9 or D9 or E9 or F9 or G9..up..to..AF9 then just the “B9 cell” should be highlighted in the left.
If I select C10 or D10 or E10 or F10 or G10……..AF10 then just the “B10 cell” should be highlighted in the left.
If I Select C11 or D11 or E11 or F11 or G11…..AF11 then just the “B11 cell” should be highlighted in the left.
If I select C12 or D12 or E12 or F12 or G12……..AF12 then just the B12 cell should be highlighted in the left

Thanks and Best Regards


Report •

#3
March 6, 2016 at 14:05:59
✔ Best Answer
Try this...

Private Sub Worksheet_SelectionChange(ByVal target As Range)
'Clear highlighting in B1:B27
   Range("B1:B27").Interior.ColorIndex = xlNone
'Check for Selection within C1:AF27
    If Not Intersect(target, Range("C1:AF27")) Is Nothing Then
'Highlight Column B in Selected row
       Range("B" & target.Row).Interior.ColorIndex = 6
    End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 6, 2016 at 18:50:01
Nice one, it worked.
Thanks a lot sir,

Report •


Ask Question