VBA - color the task, which has been sign by X

[maxblack]

Hello Collegues,

I need your help following idea. I have a table with employees where I insert, what they will be doing particular day. To make it more readable I have following idea:

When Perons 1 will choose her name, cells in row 2, where we have tasks, which have to done will be coloured. Which task should be coloured should be based on X inserted in row of that person. I try to figure it out how to do this using VBA.

I would be grateful for your support.
Thanks
max

21/04/2017

Task 1

Task 2

Task 3

Task 4

Task 5

Task 6

Task 7

Task 8

Task 9

Task 10

Task 11

Task 12

Task 13

Task 14

Task 15

P1

X

P2

X

X

P3

X

X

X

P4

X

X

X

P5

X

X

X

P6

X

X

P7

X

X

P8

X

X

P9

X

X

P10

X

X

P11

X

X

P12

X

X

<tbody>
</tbody>

I had no ide how to attach something.

 

[maxblack]

Edit: When Perons 1 will choose her name, cells in top row (Task 1, 2, 3, etc.) will be coloured based on X inserted in row of that person.

 

[MickG]

Perhaps this:-
Select range of possible "X" cells approx:-"B2:P13"
On Ribbon select Conditional Formatting.
On drop down select, "Highlight Cellrules".
From DropDown Select "Equalto",
In Dropdown Box" place an :- X
Select colour from dropdown on right handside.
Click OK .
All cell with "X" are nowcoloured.

 

[maxblack]

Thanks MickG, my question was a little bit different, but thanks, probably I described it wrongly.

The result should be as example:

I select "Person 1" cell and then Task1, Task 12 and Task 14 are going to be highlighted on yellow.
Then I select "Person 2" cell and Tasks are changing into Task 2, Task 3 and Task 8. etc etc.

I hope its clear now.

Thanks

 

[MickG]

Your logic appears to be you select "P1" (Person 1) in column "A" and the cells in that row Marked "X" are highlighted.
Meaning only the person selected will have that row highlighted, the remaining rows being not highlighted
Is that correct ???

 

[Ingolf]

@MikeG,

I think OP wants that when selecting it we say "P1" (Person 1) in Column A, to all cells marked with "X", Task 2 be highlighted. So the headers from each "X".
If selecting "P2" then highlighted: Task 9 and Task 15

 

[maxblack]

@MikeG,

I think OP wants that when selecting it we say "P1" (Person 1) in Column A, to all cells marked with "X", Task 2 be highlighted. So the headers from each "X".
If selecting "P2" then highlighted: Task 9 and Task 15

Yes, exactly Thanks Ingolf

 

[Nishant94]

Insert this code in sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MatrixRng As Range
    Dim LastColumn As Integer
    Dim Rng As Range
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1").CurrentRegion.Interior.Color = vbWhite
    If Not Intersect(Target, Range("A1:A13")) Is Nothing Then
        Set MatrixRng = Range(Target.Offset(0, 1), Target.Offset(0, LastColumn))
        For Each Rng In MatrixRng
            If Not IsEmpty(Rng) Then
                Rng.Interior.Color = vbYellow
            End If
        Next Rng
    End If
End Sub

 

[Nishant94]

Unknown
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	21/04/2017	Task 1	Task 2	Task 3	Task 4	Task 5	Task 6	Task 7	Task 8	Task 9	Task 10	Task 11	Task 12	Task 13	Task 14	Task 15
2	P1		X
3	P2									X						X
4	P3	X											X	X
5	P4				X	X						X
6	P5		X								X				X
7	P6					X				X
8	P7					X			X
9	P8						X	X
10	P9						X	X
11	P10						X					X
12	P11						X					X
13	P12	X		X
Sheet14

If you want the headings to be colored then,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MatrixRng As Range
    Dim LastColumn As Integer
    Dim Rng As Range
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1").CurrentRegion.Interior.Color = vbWhite
    If Not Intersect(Target, Range("A2:A13")) Is Nothing Then
        Set MatrixRng = Range(Target.Offset(0, 1), Target.Offset(0, LastColumn))
        For Each Rng In MatrixRng
            If Not IsEmpty(Rng) Then
                Rng.Offset(-Rng.Row + 1, 0).Interior.Color = vbYellow
            End If
        Next Rng
    End If
End Sub

 

[maxblack]

Thanks Nishant94 its working, but I have some questions:

1. Now its changing the interior color of whole table into white. Is it possible to do not touch table colours as its more readable with colours?
2. As you noticed its one day table, for next days I would like to insert same tables under each other, but then the code will not be working. If I will prepare one sheet where I will have 5 tables-days I have to insert 5 codes one for each table, am I right? I could also copy paste tables on right and hide prior day - it will be working good, but the problem will be with changing colours.
3. may you explain a little bit your code I would like to understood it better, especially from If Not...

Thanks
max