Advanced VBA Conditional Formatting & Field Population
in [Programming]
|
From: Jason Golden Jason on 13 Apr 2010 01:40 I have a rather complex spreadsheet that I would like to automate. I'm sure it can be done, just not sure how. My workbook has 3 sheets, the first is a schedule broken up into blocks, left to right the blocks are labled by the day of the week (Monday, Tuesday...) Top to Bottom labeled by the week number (1, 2, 3, 4 ,5). With in each block is a list of People (Smith; Doe...) and related elements (Room, %, Start Time). The Second sheet is a mirror of the first, data is linked to the first. This allows me to apply color coding without impacting the appearance of the first sheet. If my conditional formatting works this sheet would be eliminated. The third sheet is a key, contains a list of names from the first sheet and a corresponding Index Color ie Smith 22 My goal is to apply the related index color to all instances of smith that appear on the first sheet where the % is greater than 0. The color would only be applied to Name, Room, % and Start Time Field within a given block. Example: Smith appears in the 1 Monday, 4 Friday and 2 Tuesday Blocks. I want each instance to be colored the same ( the % field would be populated for each instance). I would attach the file to this posting, but that doesn't appear to be an option. I'm pretty sure this would require VBA code which I'm comfortable with.
From: Tim Williams on 13 Apr 2010 18:09 It's not clear from your explanation how the person, room , % and Start time are entered. Is each one in its own cell, or are they all combined in one cell ? Tim On Apr 12, 10:40 pm, Jason Golden <Jason Gol...(a)discussions.microsoft.com> wrote: > I have a rather complex spreadsheet that I would like to automate. I'm sure > it can be done, just not sure how. > > My workbook has 3 sheets, the first is a schedule broken up into blocks, > left to right the blocks are labled by the day of the week (Monday, > Tuesday...) Top to Bottom labeled by the week number (1, 2, 3, 4 ,5). > > With in each block is a list of People (Smith; Doe...) and related elements > (Room, %, Start Time). > > The Second sheet is a mirror of the first, data is linked to the first. > This allows me to apply color coding without impacting the appearance of the > first sheet. If my conditional formatting works this sheet would be > eliminated. > > The third sheet is a key, contains a list of names from the first sheet and > a corresponding Index Color ie Smith 22 > > My goal is to apply the related index color to all instances of smith that > appear on the first sheet where the % is greater than 0. > > The color would only be applied to Name, Room, % and Start Time Field within > a given block. > > Example: > > Smith appears in the 1 Monday, 4 Friday and 2 Tuesday Blocks. I want each > instance to be colored the same ( the % field would be populated for each > instance). > > I would attach the file to this posting, but that doesn't appear to be an > option. > > I'm pretty sure this would require VBA code which I'm comfortable with.
From: Jason Golden on 5 May 2010 14:18 Each is their own cell. What I want to do is color a group of cells based on the value in 2 cells... so if Surg/Grp = Block 1 and % >0 then color all three (Surg/Grp, RM & %) fields based on assigned color in index. The index would be on a separate sheet connecting the block 1 to a specific index color like Blue... so on the schedule any where Block 1 has a % > 0 color it blue (or what ever color is assigned to that block on the index sheet). Hope that clears it up. Sample: Monday Tuesday Wednesday Week 1 Surg/Grp RM % Surg/Grp RM % Surg/Grp RM % Block 1 1 80% Block 1 1 80% Block 1 1 0 Block 2 2 0 Week 2 Block 1 1 15% Block 1 1 15% Block 1 1 0 Block 2 2 10% "Tim Williams" wrote: > It's not clear from your explanation how the person, room , % and > Start time are entered. Is each one in its own cell, or are they all > combined in one cell ? > > Tim > > On Apr 12, 10:40 pm, Jason Golden <Jason > Gol...(a)discussions.microsoft.com> wrote: > > I have a rather complex spreadsheet that I would like to automate. I'm sure > > it can be done, just not sure how. > > > > My workbook has 3 sheets, the first is a schedule broken up into blocks, > > left to right the blocks are labled by the day of the week (Monday, > > Tuesday...) Top to Bottom labeled by the week number (1, 2, 3, 4 ,5). > > > > With in each block is a list of People (Smith; Doe...) and related elements > > (Room, %, Start Time). > > > > The Second sheet is a mirror of the first, data is linked to the first. > > This allows me to apply color coding without impacting the appearance of the > > first sheet. If my conditional formatting works this sheet would be > > eliminated. > > > > The third sheet is a key, contains a list of names from the first sheet and > > a corresponding Index Color ie Smith 22 > > > > My goal is to apply the related index color to all instances of smith that > > appear on the first sheet where the % is greater than 0. > > > > The color would only be applied to Name, Room, % and Start Time Field within > > a given block. > > > > Example: > > > > Smith appears in the 1 Monday, 4 Friday and 2 Tuesday Blocks. I want each > > instance to be colored the same ( the % field would be populated for each > > instance). > > > > I would attach the file to this posting, but that doesn't appear to be an > > option. > > > > I'm pretty sure this would require VBA code which I'm comfortable with. > > . >
From: Tim Williams on 5 May 2010 22:53 I'm still a little unlear on your layout. If you'd like to email me an example I will try to help you out. Tim t i m j w i l l i a m s at g m a i l dot c o m "Jason Golden" <JasonGolden(a)discussions.microsoft.com> wrote in message news:5CD12FA3-F88B-4B0D-BF6B-D494BE8205F3(a)microsoft.com... > Each is their own cell. > > What I want to do is color a group of cells based on the value in 2 > cells... > so if Surg/Grp = Block 1 and % >0 then color all three (Surg/Grp, RM & %) > fields based on assigned color in index. The index would be on a separate > sheet connecting the block 1 to a specific index color like Blue... so on > the > schedule any where Block 1 has a % > 0 color it blue (or what ever color > is > assigned to that block on the index sheet). > > Hope that clears it up. > > Sample: > Monday Tuesday > Wednesday > Week 1 Surg/Grp RM % Surg/Grp RM % > Surg/Grp RM % > Block 1 1 80% Block 1 1 > 80% Block 1 1 0 > Block 2 2 0 > > Week 2 Block 1 1 15% Block 1 1 15% > Block 1 1 0 > Block 2 2 10% > "Tim Williams" wrote: > >> It's not clear from your explanation how the person, room , % and >> Start time are entered. Is each one in its own cell, or are they all >> combined in one cell ? >> >> Tim >> >> On Apr 12, 10:40 pm, Jason Golden <Jason >> Gol...(a)discussions.microsoft.com> wrote: >> > I have a rather complex spreadsheet that I would like to automate. I'm >> > sure >> > it can be done, just not sure how. >> > >> > My workbook has 3 sheets, the first is a schedule broken up into >> > blocks, >> > left to right the blocks are labled by the day of the week (Monday, >> > Tuesday...) Top to Bottom labeled by the week number (1, 2, 3, 4 ,5). >> > >> > With in each block is a list of People (Smith; Doe...) and related >> > elements >> > (Room, %, Start Time). >> > >> > The Second sheet is a mirror of the first, data is linked to the first. >> > This allows me to apply color coding without impacting the appearance >> > of the >> > first sheet. If my conditional formatting works this sheet would be >> > eliminated. >> > >> > The third sheet is a key, contains a list of names from the first sheet >> > and >> > a corresponding Index Color ie Smith 22 >> > >> > My goal is to apply the related index color to all instances of smith >> > that >> > appear on the first sheet where the % is greater than 0. >> > >> > The color would only be applied to Name, Room, % and Start Time Field >> > within >> > a given block. >> > >> > Example: >> > >> > Smith appears in the 1 Monday, 4 Friday and 2 Tuesday Blocks. I want >> > each >> > instance to be colored the same ( the % field would be populated for >> > each >> > instance). >> > >> > I would attach the file to this posting, but that doesn't appear to be >> > an >> > option. >> > >> > I'm pretty sure this would require VBA code which I'm comfortable with. >> >> . >>
From: Jason Golden on 10 May 2010 16:45 Tim, I sent you an example via e-mail "Tim Williams" wrote: > I'm still a little unlear on your layout. > If you'd like to email me an example I will try to help you out. > > Tim > t i m j w i l l i a m s at g m a i l dot c o m > > > > "Jason Golden" <JasonGolden(a)discussions.microsoft.com> wrote in message > news:5CD12FA3-F88B-4B0D-BF6B-D494BE8205F3(a)microsoft.com... > > Each is their own cell. > > > > What I want to do is color a group of cells based on the value in 2 > > cells... > > so if Surg/Grp = Block 1 and % >0 then color all three (Surg/Grp, RM & %) > > fields based on assigned color in index. The index would be on a separate > > sheet connecting the block 1 to a specific index color like Blue... so on > > the > > schedule any where Block 1 has a % > 0 color it blue (or what ever color > > is > > assigned to that block on the index sheet). > > > > Hope that clears it up. > > > > Sample: > > Monday Tuesday > > Wednesday > > Week 1 Surg/Grp RM % Surg/Grp RM % > > Surg/Grp RM % > > Block 1 1 80% Block 1 1 > > 80% Block 1 1 0 > > Block 2 2 0 > > > > Week 2 Block 1 1 15% Block 1 1 15% > > Block 1 1 0 > > Block 2 2 10% > > "Tim Williams" wrote: > > > >> It's not clear from your explanation how the person, room , % and > >> Start time are entered. Is each one in its own cell, or are they all > >> combined in one cell ? > >> > >> Tim > >> > >> On Apr 12, 10:40 pm, Jason Golden <Jason > >> Gol...(a)discussions.microsoft.com> wrote: > >> > I have a rather complex spreadsheet that I would like to automate. I'm > >> > sure > >> > it can be done, just not sure how. > >> > > >> > My workbook has 3 sheets, the first is a schedule broken up into > >> > blocks, > >> > left to right the blocks are labled by the day of the week (Monday, > >> > Tuesday...) Top to Bottom labeled by the week number (1, 2, 3, 4 ,5). > >> > > >> > With in each block is a list of People (Smith; Doe...) and related > >> > elements > >> > (Room, %, Start Time). > >> > > >> > The Second sheet is a mirror of the first, data is linked to the first. > >> > This allows me to apply color coding without impacting the appearance > >> > of the > >> > first sheet. If my conditional formatting works this sheet would be > >> > eliminated. > >> > > >> > The third sheet is a key, contains a list of names from the first sheet > >> > and > >> > a corresponding Index Color ie Smith 22 > >> > > >> > My goal is to apply the related index color to all instances of smith > >> > that > >> > appear on the first sheet where the % is greater than 0. > >> > > >> > The color would only be applied to Name, Room, % and Start Time Field > >> > within > >> > a given block. > >> > > >> > Example: > >> > > >> > Smith appears in the 1 Monday, 4 Friday and 2 Tuesday Blocks. I want > >> > each > >> > instance to be colored the same ( the % field would be populated for > >> > each > >> > instance). > >> > > >> > I would attach the file to this posting, but that doesn't appear to be > >> > an > >> > option. > >> > > >> > I'm pretty sure this would require VBA code which I'm comfortable with. > >> > >> . > >> > > > . >
|
Next
|
Last
Pages: 1 2 Prev: Generate random numbers - Using initial seed Next: Get External Data from Web |