New to macros

[majorun]

I am 71, a long time user of Excel and am faced for the first time with the need to write a macro!!! I have a worksheet containing reservation data on various rows with various colored backgrounds and text and need all cells from column 2 to last which are not empty and white to be filled red, and text removed with a single macro. Help any one?

 

[DILIPandey]

Hi Majorun,

Can you post a sample of column 2 here ? Thanks.

Regards,
DILIPandey

 

[majorun]

Thank you so much for answering me. It is a room reservation system. What I want to do is execute a macro that will copy Sheet1 to Sheet2 and a) remove all comments and b) all cells relating to rooms 103-115 which are not white (empty) and contain text and color be changed to red without any text. Is that feasible?...I am trying to attach a copy of the file...... bu I don't seem to find how.... Let me know how to forward the file itself. Thanks in advance for your help.

 

[DILIPandey]

Hi Majorun,


You can simply copy relevant excel sheet data and paste in the reply window. Thanks.


Regards,
DILIPandey

 

[majorun]

paste does not work

 

[DILIPandey]

Hi Majorun,

Below is what I copied from excel and pasted below as an example:-

a	b	c	d
83	30	40	37
74	53	82	68
59	28	29	26
68	67	88	35
56	51	49	59
58	8	62	66
30	1	11	22
59	79	55	38

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Regards,
DILIPandey

 

[majorun]

All I get is this which does not show the color pattern and boxes. Is there an email address I can send you the file to?
RESERVATIONS 2014
Rm # HH LONG TERM SHORT TERM EMERGENCY STAY VISITS CLEANING DAYS
1-31 Jan 14
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 27 28 29 30 31
103 DMPAP
108 Mullins
109 PSP
111 VIP Vankou
112 Weibe
114 DMPAP
115 EMERGENCY SUITE

1 - 28 Feb 14
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 27 28
103 Linford MacNeil
108 Mullins
109 PSP
111 VIP Kennedy
112 Desgagnes
114 Bergeron Whipple
115 EMERGENCY SUITE

1 - 31 Mar 14
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 27 28 29 30 31
103 Rodgers
108 Mullins
109 PSP
111 VIP Saunders Thompson
112 Desgagnes Clarke
114 Thom LaPointe
115 EMERGENCY SUITE


1- 30 Apr 14
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 27 28 29 30
103 Rodgers
108 Mullins Hickman East
109 PSP
111 VIP Thompson `
112 Clarke Madore
114 Linda
115 EMERGENCY SUITE

1 - 31 May 14
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 27 28 29 30 31
103 Rodgers East
108 East Miller
109 PSP
111 VIP Thompson Marasco
112 Gander
114 Linda
115 EMERGENCY SUITE

1 - 30 Jun 14
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 27 28 29 30
103 Elston
108 Miller
109 PSP
111 VIP Marasco
112 Gander
114 Linda
115 EMERGENCY SUITE


1 - 31 Jul 14
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 27 28 29 30 31
103 Elston
108 Lowther
109 PSP
111 VIP Murasco Moss
112 Lowther
114
115 EMERGENCY SUITE

1 - 31 Aug 14
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 27 28 29 30 31
103 Elston
108 Lowther
109 PSP
111 VIP Moss
112 Lowther
114
115 EMERGENCY SUITE

1 - 30 Sep 14
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 27 28 29 30
103 Elston
108
109 PSP
111 VIP
112
114
115 EMERGENCY SUITE


1 - 31 Oct 14
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 27 28 29 30 31
103
108
109 PSP
111 VIP
112
114
115 EMERGENCY SUITE

1 - 30 Nov 14
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 27 28 29 30
103
108
109 PSP
111 VIP
112
114
115 EMERGENCY SUITE

1 - 31 Dec 14
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 27 28 29 30 31
103
108
109 PSP
111 VIP
112
114
115 EMERGENCY SUITE

 

[DILIPandey]

Not helpful

Sending the data on personal email is not a good practice.

Regards,
DILIPandey

 

[MARK858]

Not helpful

@DILIPandey, Borders-copy-paste only works (used to anyway I haven't used it for a while) if using Internet Explorer.

@majoran, see my signature block for links to some ways to post on the forum (the MrExcel HTML maker and Excel jeanie will show colors but I don't think either can display comment boxes).

 

[majorun]

I have changed all text so its ok. But I cannot get the lines of the boxes and color fields