Highlighting cells.

Tom.Jones · Dec 22, 2018

Hi,

In Sheet 1 is a calendar. Each cell is once formatted to show only numbers from 1 to 31 (obviously, depending on the last day's month may be 28, 29, 30 or 31).
In Sheet 2 in column A starting with A2 some data are passed, and in column B are the names of those data.
I would like to be able to put certain colors in certain cells in Sheet 2 (say in Column C) and the data in Column A be highlighted in the same color in the Sheet 1 calendar.

Let's say that in A2 sheet 2, it is 01.02.2019, in B2 is the name of that day and in C2 I put the red color. I would like the calendar date of 01.02.2019 (dd.mm.yyyy) to be colored in red.
Because there is a lot of data I would like to highlight I would like to not use Condition Formatting, but a VBA code.

Is that possible?
Thank you.

Tom.Jones · Dec 23, 2018

It is not possible???

Some ideas....

mumps · Dec 23, 2018

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Tom.Jones · Dec 23, 2018

Wrong post....

Tom.Jones · Dec 23, 2018

Removed image code

Fluff · Dec 23, 2018

@Tom.Jomes
You cannot post images to the board.
Please use one of the add-ins available here to post some sample data

Tom.Jones · Dec 23, 2018

Thanks mumps and Fluff.

Yes, I have seen that I can not post pictures, but unfortunately I can not install anything on my computer due to company policies.
I do not know what I can do.

Let's try to simplify the problem.
If in Sheet 2, there are 3 calendar dates (say 01.02.2019, 19.04.2019 and 27.05.2019) and the adjacent cells are filled in red.
And in sheet 1 there are more data, and among those data are those in the sheet2.
Using a VBA code, I want to highlight those cells in Sheet 1, which contains the data in Sheet 2 (coloring them in red).

Thanks.

mumps · Dec 23, 2018

Try:

Code:

Sub colorCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, fRng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Sheet2").Range("A2:A" & LastRow)
        Set fRng = Sheets("Sheet1").Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not fRng Is Nothing Then
            fRng.Interior.ColorIndex = rng.Offset(0, 1).Interior.ColorIndex
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Tom.Jones · Dec 23, 2018

Data in Sheet 1 are like:

2019

Jan

Feb

Mar

Event 1

If in DV is Event 1 then show ONLY date in RED

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

If in DV is Event 2 then show ONLY date in Orange

1

2

3

4

5

6

1

2

3

1

2

3

and so on…

7

8

9

10

11

12

13

4

5

6

7

8

9

10

4

5

6

7

8

9

10

14

15

16

17

18

19

20

11

12

13

14

15

16

17

11

12

13

14

15

16

17

In B6 is first of January

21

22

23

24

25

26

27

18

19

20

21

22

23

24

18

19

20

21

22

23

24

28

29

30

31

25

26

27

28

1

2

3

25

26

27

28

29

30

31

4

5

6

7

8

9

10

1

2

3

4

5

6

7

Apr

Mai

Jun

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

1

2

3

4

5

6

7

29

30

1

2

3

4

5

27

28

29

30

31

1

2

8

9

10

11

12

13

14

6

7

8

9

10

11

12

3

4

5

6

7

8

9

15

16

17

18

19

20

21

13

14

15

16

17

18

19

10

11

12

13

14

15

16

22

23

24

25

26

27

28

20

21

22

23

24

25

26

17

18

19

20

21

22

23

29

30

1

2

3

4

5

27

28

29

30

31

1

2

24

25

26

27

28

29

30

6

7

8

9

10

11

12

3

4

5

6

7

8

9

1

2

3

4

5

6

7

Jul

Aug

Sep

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

1

2

3

4

5

6

7

29

30

31

1

2

3

4

26

27

28

29

30

31

1

8

9

10

11

12

13

14

5

6

7

8

9

10

11

2

3

4

5

6

7

8

15

16

17

18

19

20

21

12

13

14

15

16

17

18

9

10

11

12

13

14

15

22

23

24

25

26

27

28

19

20

21

22

23

24

25

16

17

18

19

20

21

22

29

30

31

1

2

3

4

26

27

28

29

30

31

1

23

24

25

26

27

28

29

5

6

7

8

9

10

11

2

3

4

5

6

7

8

30

1

2

3

4

5

6

Oct

Nov

Dec

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

M

Tu

W

Th

F

S

30

1

2

3

4

5

6

28

29

30

31

1

2

3

25

26

27

28

29

30

1

7

8

9

10

11

12

13

4

5

6

7

8

9

10

2

3

4

5

6

7

8

14

15

16

17

18

19

20

11

12

13

14

15

16

17

9

10

11

12

13

14

15

21

22

23

24

25

26

27

18

19

20

21

22

23

24

16

17

18

19

20

21

22

28

29

30

31

1

2

3

25

26

27

28

29

30

1

23

24

25

26

27

28

29

4

5

6

7

8

9

10

2

3

4

5

6

7

8

30

31

1

2

3

4

5

and in sheet2

Events	Start	End	Color
Event 1	08.01.2019	11.01.2019
Event 2	25.02.2019	28.02.2019
Event 3	07.02.2019	10.02.2019
Event 4	18.03.2019	27.03.2019
Event 5	01.04.2019	05.04.2019
Event 6	27.05.2019	27.05.2019

Now you can see how is data.

Tom.Jones · Dec 23, 2018


									2019											DV

Jan		Feb		Mar			Event 1
M	Tu	W	Th	F	S	S		M	Tu	W	Th	F	S	S	M	Tu	W	Th	F	S	S
31	1	2	3	4	5	6		28	29	30	31	1	2	3	25	26	27	28	1	2	3
7	8	9	10	11	12	13		4	5	6	7	8	9	10	4	5	6	7	8	9	10
14	15	16	17	18	19	20		11	12	13	14	15	16	17	11	12	13	14	15	16	17
21	22	23	24	25	26	27		18	19	20	21	22	23	24	18	19	20	21	22	23	24
28	29	30	31	1	2	3		25	26	27	28	1	2	3	25	26	27	28	29	30	31
4	5	6	7	8	9	10		4	5	6	7	8	9	10	1	2	3	4	5	6	7

Apr		Mai		Jun
M	Tu	W	Th	F	S	S		M	Tu	W	Th	F	S	S	M	Tu	W	Th	F	S	S
1	2	3	4	5	6	7		29	30	1	2	3	4	5	27	28	29	30	31	1	2
8	9	10	11	12	13	14		6	7	8	9	10	11	12	3	4	5	6	7	8	9
15	16	17	18	19	20	21		13	14	15	16	17	18	19	10	11	12	13	14	15	16
22	23	24	25	26	27	28		20	21	22	23	24	25	26	17	18	19	20	21	22	23
29	30	1	2	3	4	5		27	28	29	30	31	1	2	24	25	26	27	28	29	30
6	7	8	9	10	11	12		3	4	5	6	7	8	9	1	2	3	4	5	6	7

Jul		Aug		Sep
M	Tu	W	Th	F	S	S		M	Tu	W	Th	F	S	S	M	Tu	W	Th	F	S	S
1	2	3	4	5	6	7		29	30	31	1	2	3	4	26	27	28	29	30	31	1
8	9	10	11	12	13	14		5	6	7	8	9	10	11	2	3	4	5	6	7	8
15	16	17	18	19	20	21		12	13	14	15	16	17	18	9	10	11	12	13	14	15
22	23	24	25	26	27	28		19	20	21	22	23	24	25	16	17	18	19	20	21	22
29	30	31	1	2	3	4		26	27	28	29	30	31	1	23	24	25	26	27	28	29
5	6	7	8	9	10	11		2	3	4	5	6	7	8	30	1	2	3	4	5	6

Oct		Nov		Dec
M	Tu	W	Th	F	S	S		M	Tu	W	Th	F	S	S	M	Tu	W	Th	F	S	S
30	1	2	3	4	5	6		28	29	30	31	1	2	3	25	26	27	28	29	30	1
7	8	9	10	11	12	13		4	5	6	7	8	9	10	2	3	4	5	6	7	8
14	15	16	17	18	19	20		11	12	13	14	15	16	17	9	10	11	12	13	14	15
21	22	23	24	25	26	27		18	19	20	21	22	23	24	16	17	18	19	20	21	22
28	29	30	31	1	2	3		25	26	27	28	29	30	1	23	24	25	26	27	28	29
4	5	6	7	8	9	10		2	3	4	5	6	7	8	30	31	1	2	3	4	5

In AA4 I put a DV with Event1 to 6

If is more easy in this way ...

Highlighting cells.

Tom.Jones

Well-known Member

Excel Facts

Tom.Jones

Well-known Member

mumps

Well-known Member

Tom.Jones

Well-known Member

Tom.Jones

Well-known Member

Fluff

MrExcel MVP, Moderator

Tom.Jones

Well-known Member

mumps

Well-known Member

Tom.Jones

Well-known Member

Tom.Jones

Well-known Member

Similar threads

Forum statistics

Share this page

Highlighting cells.

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock