Problem with merged cells

mutrus · Apr 2, 2004

The display below has not quite come out properly but it should show all the days of the month. It is an activity planner. There are also lots more rows

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

(UsethespacebelowtoenterMonthlyTasks)

2

3

4

5

T

F

S

M

T

W

T

F

S

M

T

W

T

F

S

M

T

W

T

F

S

M

6

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

7

HQStaff

8

9

10

11

ProjectsStaff

Sheet1

There is a button on the menu that shows a user form from which a date is selected. The code then colours the selected column grey which signifies that this day is a Holiday.

My problem is how to skip over the rows that contain merged cells that cover the selected column. I already have code that can cope with merged cells that cover the entire row (example rows 7, 11 & 15) but not row 8 if I select column I for the holiday

So what I need is code to do this: If I want Friday 9th to be a holiday then all cells in range I5:I15 to be coloured grey except those cell that are merged

Hope this is not too confusing

erik.van.geit · Apr 2, 2004

I'm interested in this too.
Testing already since this is published didn't find anything neigther that worked in one operation.
But this works.

Code:

Sub color()
For Each ccc In Range("I5:I15")
ccc.Select
If Selection.Column = Range("I15").Column Then ccc.Interior.ColorIndex = 15
Next ccc
End Sub

have a nice time with all these beautiful colors
Erik

mutrus · Apr 3, 2004

That works except it also colors the merged cells. I need to keep the merged cells original colors

mutrus · Apr 3, 2004

Erik

You mentioned 'you are interested in this too'

Does this mean you are interested in what my workbook does or interested in the answer to my question (because you've almost answered it)?[/quote]

Ahnold · Apr 3, 2004

Mutrus,

Try this code:

Code:

Sub Macro1()
    Dim myRange As Range
    Dim y As Integer
    x = InputBox("Enter the Day (1-31).")
    If x = "" Then Exit Sub ' User pressed cancel button
    If IsNumeric(x) Then
        y = Rows("6:6").Find(What:=x, LookIn:=xlValues).Column
        Set myRange = Range(Cells(5, y), Cells(15, y))
        For Each c In myRange
            If c.MergeCells = True Then
                Else
                    c.Interior.ColorIndex = 15
            End If
        Next c
    End If
End Sub

Barry Katcher · Apr 3, 2004

Mutrus, the technical explanation of what is happening is this: merged cells suck! They're much more trouble than they're worth. Before trying to figure out code to circumvent your problems, you might want to try this: unmerge your cells, and try formatting them to Format>Cells>Alignment>Horizontal>Center Across Selection. Practically the same results, without the hassle.

Barry

mutrus · Apr 3, 2004

Ahnold - perfect - thanks

Barry - I agree - your suggestion is noted thanks

Others - thanks

erik.van.geit · Apr 3, 2004

mutrus,

To answer to your question of 10:40. I was interested in the solutions.
Barry, thanks for this solution!

bye,
Erik

Problem with merged cells

mutrus

Board Regular

Excel Facts

erik.van.geit

MrExcel MVP

mutrus

Board Regular

mutrus

Board Regular

Ahnold

Well-known Member

Barry Katcher

Well-known Member

mutrus

Board Regular

erik.van.geit

MrExcel MVP

Similar threads

Forum statistics

Share this page

Problem with merged cells

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Board Regular

Well-known Member

Well-known Member

Board Regular

MrExcel MVP

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