Set the color of grid depend on variable

I'd like to set the color of cells (sheet1)

[A1...A8] -> Red
[A9...A28] -> Green
[A29...A44] -> Blue
[A45...A54] -> Red
....

based on data from sheet2

Start   Stop      Color
1          8           Red
9          28         Green
29        44         Blue
45        54         Red
.....




How to do it ?

--




~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Radoslaw Krzyzan , pl.communicator@radek
http://www.communicator.pl , ICQ 4336523
~~~~~~~~~~~~~~~~~~~~~~~~~~~~



0
noone8378 (1)
5/15/2004 12:41:23 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
593 Views

Similar Articles

[PageSpeed] 21

Hi
for only three colors have a look at 'format - conditional Format'. For
more than 3 conditions you need VBA

--
Regards
Frank Kabel
Frankfurt, Germany

"Radoslaw Krzyzan" <noone@onet.pl> schrieb im Newsbeitrag
news:c853as$m5e$1@nemesis.news.tpi.pl...
> I'd like to set the color of cells (sheet1)
>
> [A1...A8] -> Red
> [A9...A28] -> Green
> [A29...A44] -> Blue
> [A45...A54] -> Red
> ...
>
> based on data from sheet2
>
> Start   Stop      Color
> 1          8           Red
> 9          28         Green
> 29        44         Blue
> 45        54         Red
> ....
>
>
>
>
> How to do it ?
>
> --
>
>
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Radoslaw Krzyzan , pl.communicator@radek
> http://www.communicator.pl , ICQ 4336523
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>

0
frank.kabel (11126)
5/15/2004 1:04:18 PM
If you set up a table like this where col a-B is the range and col c has the
color index NUMBER.
      1 8 3
      9 28 4
      29 44 37
      45 54 3

Then, this will work

Sub setcolor()
For Each c In range("a1:a4")
x = c.Offset(0,1)
Sheets("sheet1").Range("c" & c & ":c" & x) _
..Interior.ColorIndex = c.Offset(, 2)
Next c
End Sub

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Radoslaw Krzyzan" <noone@onet.pl> wrote in message
news:c853as$m5e$1@nemesis.news.tpi.pl...
> I'd like to set the color of cells (sheet1)
>
> [A1...A8] -> Red
> [A9...A28] -> Green
> [A29...A44] -> Blue
> [A45...A54] -> Red
> ...
>
> based on data from sheet2
>
> Start   Stop      Color
> 1          8           Red
> 9          28         Green
> 29        44         Blue
> 45        54         Red
> ....
>
>
>
>
> How to do it ?
>
> --
>
>
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Radoslaw Krzyzan , pl.communicator@radek
> http://www.communicator.pl , ICQ 4336523
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>


0
Don
5/15/2004 1:55:51 PM
You can use a Worksheet_Change event to change the colours. The 
following code should go on the code module for Sheet2. Right-click the 
sheet tab, and choose View Code. Paste the code where the cursor is 
flashing.

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Dim c As Range
Dim cColour As Integer
Dim r As Long
Dim ws1 As Worksheet
Const RED = 3
Const BLUE = 5
Const GREEN = 10

r = Cells(Rows.Count, 1).End(xlUp).Row
Set ws1 = Worksheets("Sheet1")
ws1.Columns(1).Interior.ColorIndex = 0
For Each c In Range(Cells(2, 1), Cells(r, 1))
   Select Case UCase(c.Offset(0, 2).Value)
     Case "RED": cColour = RED
     Case "BLUE": cColour = BLUE
     Case "GREEN": cColour = GREEN
     Case Else: cColour = 0
   End Select

   ws1.Range(ws1.Cells(c.Value, 1), ws1.Cells(c.Offset(0, 1).Value, 1)) _
     .Interior.ColorIndex = cColour
Next c
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & "--" & Err.Description
Exit Sub

End Sub
'=========================================

Radoslaw Krzyzan wrote:
> I'd like to set the color of cells (sheet1)
> 
> [A1...A8] -> Red
> [A9...A28] -> Green
> [A29...A44] -> Blue
> [A45...A54] -> Red
> ...
> 
> based on data from sheet2
> 
> Start   Stop      Color
> 1          8           Red
> 9          28         Green
> 29        44         Blue
> 45        54         Red
> ....
> 
> 
> 
> 
> How to do it ?
> 
> --
> 
> 
> 
> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Radoslaw Krzyzan , pl.communicator@radek
> http://www.communicator.pl , ICQ 4336523
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/15/2004 2:37:46 PM
Reply:

Similar Artilces:

should variable declaration will be changed??
THis code compiles ok when i click priceqoute botton it display request id but when it comes to next it give a erreor message "invalid market no",and "invalid amount" Can anybody suggest me how to come out of this....should i change the variable type or what... void CWartz_newDlg::OnBpricequote() { // TODO: Add your control notification handler code here int pageno(0); requestid=m_arielapi.RequestPrices(sessionid,pageno); CString displaydata; displaydata.Format(_T("==>Request ID [%s]"),requestid); LOG(0,displaydata); BOOL bSuccess=FALSE; for(int n=0;n<3...

Why Excel changes colors of my charts ?
After I have finished updating my charts and open them again, Excel has automatically changed the colors of the charts. How can I turn this option off ? Are these charts from pivot tables? "Ude" wrote: > After I have finished updating my charts and open them again, Excel has > automatically changed the colors of the charts. How can I turn this option > off ? > > ...

Custom settings now working
I am having problems with the custom security settings. I installed the security administrative package and followed Microsoft's documentation. However, I am still getting the Outlook notification whenever I try to use CDO. I am not an Outlook admin so I am having a rough time trying to debug this. What are some things I can look at to try to resolve this issue. I noticed that when I go to Outlook and go the Outlook 10 Security Settings under public folders I see the form under that folder. Is this correct? My set up is Exchange 2000, Outlook 2003. Have you checked to make sure th...

Toolbar Items reverting to the Default Settings
I have been unable to find an answer to this problem for more than a year, and thought maybe someone on this newsgroup would be able to tell me what to do about my problem!! I have certain "custom" settings that I like on my Outlook toolbars, and for some unexplained reason, sometimes several times in the same day, the settings I custom designed disappear, and I'm back to the Default settings. Is there something I can do to prevent this from happening?? EADIE Silverberg eadies@bigfoot.com ...

How to set schedule task?
When I set task based on schedule, it requests to input the passwords, but I don't set any password on my XP with SP3, and the task cannot be performed. Does anyone have any suggestions on how to set task without passwords? Thanks in advance for any suggestions Eric "Eric" <Eric@discussions.microsoft.com> wrote in message news:D6396742-7F6D-424A-A203-6677F0F97A4F@microsoft.com... > When I set task based on schedule, it requests to input the passwords, but > I > don't set any password on my XP with SP3, and the task cannot be > performed. ...

The ability to set the naming convention for EFT files.
Some banks have limits on the length of the file name that can be submitted for EFT. With the new naming convention the file names are all to long. Can you devise a method for setting the namin converntion. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www....

How do I change the text color of a PDF documents?
How do I change the text color of a PDF documents. The text shows up blue automatically and there is no option to change it to other colors. It is a PDF Foxit Reader 2.2. When I print it, it shows up as grey color. Not anything that Office has control over. Mybe use a different PDF reader. TLC_RN wrote: > How do I change the text color of a PDF documents. The text shows up blue > automatically and there is no option to change it to other colors. It is a > PDF Foxit Reader 2.2. When I print it, it shows up as grey color. You can't edit a .pdf in any of the Off...

Centering, grids
I'm new to this software, using Publisher 2002. I'm working on a tri-fold brochure. How can I center a text box in one of the side panels? I've tried Arrange /Align Center and /Distribute Horizontally but that puts my text box in the center of the entire document, in the center panel. Also, in other programs I'm used to working with gridlines showing, but I can't find any such thing in Publisher. I'd like something that will help me visually balance the placement of the objects. Any help would be appreciated. Thanks. The guide lines are brought about by p...

how do I change the default setting of the "TO" on e-mails?
When doing an e-mail and I click the "to" it defaults my selection to "contact. How do I change the default to "Global Address List"? Tools, Address book, Tools, Options. mikie v wrote: > When doing an e-mail and I click the "to" it defaults my selection to "contact. > How do I change the default to "Global Address List"? ...

Integration Site Enabler
Integration Site Enabler access is granted to users and they are able to access Integration Manager with no issues. However, later in the day or even a few days later, the user no longer has access and it is unmarked in the Security window. This happens on a regular basis and is not an isolated incident. The users access a terminal server via remote desktop, roaming profiles are not used, and it is not isolated to any one user. Thanks! ------=_NextPart_0001_431DD987 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Nola You can use use a number of methods to track this. 1) ...

List box
This is probably very simple. I have a list box (using control toolbox) in a work sheet and I want to specify that every time the workbook is opened that the first item on the list box is always selected Thanks Brian Activesheet.Listbox1.ListIndex = 0 -- HTH RP (remove nothere from the email address if mailing direct) "Brian" <Brian@discussions.microsoft.com> wrote in message news:32B7340F-05C9-47B5-B275-3FCFEBB2FDE6@microsoft.com... > This is probably very simple. > > I have a list box (using control toolbox) in a work sheet and I want to > specify ...

Setting up a spreadsheet
Hello, I need helPPPPPPP. I am trying to setup a spreadsheet with the following format and I am not sure how to in put the calculations. Here is what I have design by hand. 1. Monthly Assessment | Parking | Total Due 2. Previous Balance 3. Prepaid Amount 4. Balance 5. Paid 6. Late Fee 7. Other Charges 8. Credit Balance 9. Past Due Balance Thanks for any direction and assist you may be able to render. Thanks, Shannon :-) Thanks for the reply, Basically what I am saying is how would I create a automatic formula for each of the listed items. For example for the fir...

What UAC default setting if W2k8 join AD (assuming GPO not yet been configured in AD)
Dear all, Could you please tell me, after Win server 2008 joined AD domain, what is the default setting for UAC ? ( gpedit.msc >> Computer Configuration >> Windows Settings >>Security >> Local Policy >> Security Options .. look for UAC setting) My current Windows Server 2008 UAC setting, before join AD domain, http://img687.imageshack.us/img687/4971/20091114sfwin2008uacset.png I would like to know if after join domain the default setting for UAC ? assuming the AD environment is has not yet configured the GPO policies. Thank you in advance. Hell...

How do I set up a private session with a webcam?
I want to be able to use my webcam to talk to my daughter stationed in Italy. She has a webcam also. How do I go about doing this? Outlook doesn't have any webcam functions; you might want to try using MSN Messenger for this. http://messenger.msn.com/ -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** "alwaysewing" wrote: > I want to be able to use my webcam to talk to my daughter stationed in Italy. > She has a webcam also. How do I go about doin...

How to insert new pop3 settings in curent user outlook environment via script or command line?
I have environment of 5000 + computers & I need to set up additional pop3 acces on users environment.. I have tool that reads custom data & I want to assign pop3server, login, snmp server data as new account in curent user environment, since my tool operates via command line I would like to use some script that can do such job. Targeted version is ooutlook 2003 Regards. "Ram Demon" <globalmapleplayer@gmail.com> wrote in message news:%23Ro6ciuoJHA.6132@TK2MSFTNGP06.phx.gbl... >I have environment of 5000 + computers & I need to set up additional pop...

can't set up online services for downloading account information
I have Microsoft Money 2002 and have been able to download my account information for several credit cards without using the "Set up online services" function, but Discover Card requires use of that function to initiate downloads. Bu whenever I try to do that for any credit card I get a window briefly saying that Microsoft is downloading information from my financial institution, but then I get an error message: "Money cannot complete a required online call at this time. Please check your Internet connection settings and try again." I have a broadband DSL connect...

Loosing Grid ?
On some cells when I mess with format I am loosing the grid on some cells. This is not a border but just the grid outlining each cell. I though you turn the entire gird on or off for the spreadsheet but where does it get modified for each cell ?? Hi If you format a cell with a white pattern (background) you will not be able to see the gridlines of it. -- Andy. "Al Franz" <albert@nospam.netmation.com> wrote in message news:ePjIBzEdEHA.3616@TK2MSFTNGP10.phx.gbl... > On some cells when I mess with format I am loosing the grid on some cells. > This is not a border bu...

Crm grid error
I've created a custom entity called Billed hours and associated it to the case entity (which is the parent). It doesn't allow me to open the instance of the billed hours entity in the associated view and when i select it and try to delete it, it says: "You must select one or more records before you can perform this action". I'd appreciate any help :) ...

Exchange mailbox rights set as noninherited #2
Hello, We have quite strange problem, with mailbox exchange rights inherited, as well as explicitly set. For example... There are two mailboxes... lets call them "dummy1" and "dummy2"... At the storage group with these mailboxes permissions are applied for group exchange administrators, which say that the group should not have access to the mailboxes in the storage group. If I check the permissions at those two mailboxes, DUMMY1 shows permissions inherited from parent object, as it should be... everything ok. But DUMMY2 shows permissions explicitly configured at the o...

Regression with more than 16 variables
I noticed linest and the regrssion tool doesn't allow more than 16 variables. Does anyone know the most convenient way to do this? I currently don't have SAS. Is it possible in Access or anything else? Without being pessimistic I think you'll struggle to do this mutlivariate regression in excel. There may be a way but it will be long-winded I am sure. As a suggestion, stats websites often allow you to input data and see the output running off Javascript as an example. Try and find a website that you can cut and paste your data to and crank the analysis... SAS could do it ea...

How to test the text font color in a cell
Hello, I would like to test if the text font in a cell is red or black. Is there a way using any Excel function (i did not find any), or a VBA macro? Thanks for any answer Yves click on a cell that has text in either red or black colour and then ru this macro. it would tell you the colour. i found out the colorindex b recording a macro, sometimes the easiest and quickest way to solv these kinds of problems. Option Explicit Sub TestColour() If ActiveCell.Font.ColorIndex = 3 Then MsgBox "red" If ActiveCell.Font.ColorIndex = 1 Then MsgBox "black" If ActiveCell.Font.Col...

How do I set it up so that the table and form shows 2 decimal plac
Hi In my database, I have a field that deals with time spent on an activity. The desired time interval is 0.25 for every 15 minutes. For example, if a person spent 30 minutes on an activity, I want 0.5 to be the value. What is the proper way to set this up? I tried Fixed, General, Scientific. The number entered is always rounded up. Thanks >>I tried Fixed, General, Scientific. Open your table in design view and check the properties for your number field to see if it is Number - Long Integer. If so then change to Single or Double. -- Build a little, tes...

Setting Bank's web address
I've tried to change my bank's web site address so that I can go directly to the sign in page instead of having to click through several links from the bank's home page. (If I manually type the address, my browser will go directly to this page.) However, whenever I "connect to my bank" from within the Money menu for an account, it always goes to the bank's home page. What's happening here. How can I change this connection? In microsoft.public.money, David E wrote: >I've tried to change my bank's web site address so that I >can go directly ...

Set Record Source to two queries?
Using Office 2003 and Windows XP; I have a report whose Record Source is set to a query; Question: Is there a way to make it point to two queries? If not, how can I reference or return some results from a second query in my report? The results will be many rows grouped a certain way... Thanks much for your help. The RecordSource of the Report can hold more then one Query/Table, as long that you can link between them (Join fields), other wise it will multiply your records. If there is no field that link between the queris you can use sub report for the second query. Or, if you are lo...

setting font size to decimal number
Hi all, In the CFontDialog I am not able to set up decimal numbers, like 7.5. It works in MS Word (on the same box, the same font). Getting message "Size must be a number." Any ideas? Thanks in advance! Vaclav Hi Vaclav: > In the CFontDialog I am not able to set up decimal numbers, like 7.5. > It works in MS Word (on the same box, the same font). > Getting message "Size must be a number." > > Any ideas? Just a thought: Do you have the correct settings in the regional setting? Just check if there is jused a ',' as decimal char. HTH Alex -- ...