Colorising cells from code

I am using the code below which runs whenever a cell is edited -I think.

I would like to change this code so that it can be 'run' against a range 
of cells rather than as Worksheet_Change code.

What do I need to do to do that ?

Thanks

Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
  If Range("A1").Value = "" Then
    If Not Intersect(Target, Range("B4:J34, B35:B39")) Is Nothing Then
    Select Case Target
      Case Sheet3.Range("A4")
        icolor = 34
      Case Sheet3.Range("A5")
        icolor = 35
      Case Sheet3.Range("A6")
        icolor = 38
      Case Sheet3.Range("A7")
        icolor = 36
      Case Sheet3.Range("A8")
        icolor = 37
      Case Sheet3.Range("A9")
        icolor = 33
      Case Else
      'icolor = 50
      'Whatever
    End Select
    
    Application.EnableEvents = False
      If icolor > 30 And icolor < 51 Then
        Target.Interior.ColorIndex = icolor
      End If
    Application.EnableEvents = True
    End If
  End If
End Sub
0
Isis
4/26/2010 4:51:10 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
808 Views

Similar Articles

[PageSpeed] 18

Not quite sure what you want.

Sub Tryme()
Dim icolor As Integer
icolor = 34
For Each mycell In Selection
  mycell.Interior.ColorIndex = icolor
  icolor = icolor + 1
  If icolor > 45 Then icolor = 34
Next
End Sub

Select a range of cells and run this one to get different colours
The 45 max is quite arbitrary
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme






"Isis" <isissoft@NOSPAMbtinternet.com> wrote in message 
news:Xns9D66B5B407BC6isissoft@193.202.122.117...
> I am using the code below which runs whenever a cell is edited -I think.
>
> I would like to change this code so that it can be 'run' against a range
> of cells rather than as Worksheet_Change code.
>
> What do I need to do to do that ?
>
> Thanks
>
> Sub Worksheet_Change(ByVal Target As Range)
> Dim icolor As Integer
>  If Range("A1").Value = "" Then
>    If Not Intersect(Target, Range("B4:J34, B35:B39")) Is Nothing Then
>    Select Case Target
>      Case Sheet3.Range("A4")
>        icolor = 34
>      Case Sheet3.Range("A5")
>        icolor = 35
>      Case Sheet3.Range("A6")
>        icolor = 38
>      Case Sheet3.Range("A7")
>        icolor = 36
>      Case Sheet3.Range("A8")
>        icolor = 37
>      Case Sheet3.Range("A9")
>        icolor = 33
>      Case Else
>      'icolor = 50
>      'Whatever
>    End Select
>
>    Application.EnableEvents = False
>      If icolor > 30 And icolor < 51 Then
>        Target.Interior.ColorIndex = icolor
>      End If
>    Application.EnableEvents = True
>    End If
>  End If
> End Sub 

0
Bernard
4/26/2010 7:09:36 PM
"Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote in 
news:etpOEQX5KHA.5476@TK2MSFTNGP06.phx.gbl:

> Not quite sure what you want.
> 
> Sub Tryme()
> Dim icolor As Integer
> icolor = 34
> For Each mycell In Selection
>   mycell.Interior.ColorIndex = icolor
>   icolor = icolor + 1
>   If icolor > 45 Then icolor = 34
> Next
> End Sub
> 
> Select a range of cells and run this one to get different colours
> The 45 max is quite arbitrary
> best wishes

Bernard, thanks for taking the time to look at this for me. I did not 
make myself clear - I am trying to colour cells based on their contents - 
in this instance names.

My names are on another sheet - sheet3.Range(A4:A9)

I want to go through a Range and color the cells based on the name they 
have in them (they are all filled from Drop Downs) - so in my original 
code I have;
Case Sheet3.Range("A6")
        icolor = 38

Can your code be adapted to do that - I am using vba.

Thanks
0
Isis
4/26/2010 11:09:17 PM
What range of cells are to get colours
What range of cells have data
Please explain how to the data determines the colour  - give some examples
When you say 'names' I understand you to mean 'text'
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Isis" <isissoft@NOSPAMbtinternet.com> wrote in message 
news:Xns9D671A8B6A52isissoft@91.208.207.173...
> "Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote in
> news:etpOEQX5KHA.5476@TK2MSFTNGP06.phx.gbl:
>
>> Not quite sure what you want.
>>
>> Sub Tryme()
>> Dim icolor As Integer
>> icolor = 34
>> For Each mycell In Selection
>>   mycell.Interior.ColorIndex = icolor
>>   icolor = icolor + 1
>>   If icolor > 45 Then icolor = 34
>> Next
>> End Sub
>>
>> Select a range of cells and run this one to get different colours
>> The 45 max is quite arbitrary
>> best wishes
>
> Bernard, thanks for taking the time to look at this for me. I did not
> make myself clear - I am trying to colour cells based on their contents -
> in this instance names.
>
> My names are on another sheet - sheet3.Range(A4:A9)
>
> I want to go through a Range and color the cells based on the name they
> have in them (they are all filled from Drop Downs) - so in my original
> code I have;
> Case Sheet3.Range("A6")
>        icolor = 38
>
> Can your code be adapted to do that - I am using vba.
>
> Thanks 

0
Bernard
4/27/2010 1:55:37 AM
"Bernard Liengme" <bliengme@TRUENORTH.stfx.ca> wrote in
news:u2zB8ya5KHA.4264@TK2MSFTNGP02.phx.gbl: 

> What range of cells are to get colours
> What range of cells have data
> Please explain how to the data determines the colour  - give some
> examples When you say 'names' I understand you to mean 'text'
> best wishes

Bernard - again, thanks for the reply. Apologies for not being exact in 
my definitions.

The sheet is a work timesheet
The 'Names' are peoples names
Part of the sheet contains cells representing work shifts
Each Work Sheet cell has a drop down where you cn pick a staff member
Once picked - the cell gets colored differently for each Name
All same Names have same color

This works well using 'Worksheet_Change' code - but, I also would like to 
be able to write out large blocks of names using vba (which I can do) and 
then 'zip' through them all coloring them according to my standard name 
coloring code afterwards.

So I have an 'automatic' way of coloring cells as they are changed by the 
user, but I would also like a 'manual' way that I can use when I need - a 
coded (routine) that I can call from wherever.

Sorry for not setting out my actual situation clearly - I really 
appreciate the help on offer here,

Thanks
0
Isis
4/27/2010 9:49:08 AM
Reply:

Similar Artilces:

Inserting Quotation Marks in all Cells
Hi! I have a co-worker who needs to insert quotation marks in the entire worksheet. It contains both numbers and text. The quotation marks need to be inserted around each individual cells data. Can someone help? Enter this small macro: Sub quoteit() Dim r As Range For Each r In Selection If IsEmpty(r.Value) Then Else r.Value = Chr(34) & r.Value & Chr(34) End If Next End Sub Select all or some portion of the worksheet and run the macro. It will put quote marks before and after the contents of all selected non-empty cells -- Gary's Student "confused" wrote: ...

XL2003: Move a Comment Into a Different Cell?
Is it possible to move a comment into a different cell? For example: I have a comment in cell B4; can I move it to cell A4? I realize that I could always copy the comment to the new cell and then delete the comment in the old cell, but surely there's an easier way? Copy-and-delete becomes tedious when you have to do it for dozens of cells individually. Daddy You could use a macro... Option Explicit Sub testme() Dim FromCell As Range Dim ToCell As Range Set FromCell = Nothing On Error Resume Next Set FromCell = Application.InputBox ...

Incert a photo into a cell based on info in another cell
Hey, I need to insert a photo into a cell (a cell that has been merged btw) based on the information in a cell (i.e. A1). This cell (A1) uses list validation and allows one to choose from a list of names. What I want to do is put a photo into a cell (say B1) from a folder (say C:\Photos\) based on the name that is selected in A1. Make sense? azidrane, have a look here and see if this will do what you want http://www.mcgimpsey.com/excel/lookuppics.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Reference cell error
Hi When I use the following formula, I get #REF error. How can I fix it? Thank you =SUM('Daily Sworn Status'!M8) probably a misspelling of the sheet name da;722686 Wrote: > Hi > When I use the following formula, I get #REF error. > How can I fix it? > Thank you > > =SUM('Daily Sworn Status'!M8) -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/showthread.ph...

changeText formatting in cell
Does anyone know a quick way to change a group of text cells to ALL CAPS? Thanks Manually.......insert a column and enter.......... =UPPER(A1) Darg/copy down the column. Programatically.......... On Thu, 12 Aug 2004 16:51:38 -0700, "grr" <anonymous@discussions.microsoft.com> wrote: >Does anyone know a quick way to change a group of text >cells to ALL CAPS? Thanks Now I'll finish the posting Sub Upper_Case() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim cell As Range Application.ScreenUpdating...

Linking cells: open source to update values
Hi there, I created a source file (.csv) that contains two columns and several rows. This source file is filled (updated) by a SQL process that runs everyday. The source file is input for a report in Excel. I would like to automatically update the Excel report when it is opened. However, although it looks like I linked the correct file, worksheet and range, I can only update the Excel report after I manually open the source file. But that's not what I want, I don't want to see the source file. Anyone? -- zimon72 ------------------------------------------------------------------...

how do I fill in a cell with text in Excel macro (2003)
Hi, I am running a macro where I copy and paste values from workbook to another. Is it possible to fill in a cell with text without having to copying it from another workbook. Kind regards, Rikin > Is it possible to fill in a cell with text without having to copying it from > another workbook. Yes Range("A1").value="Sometext" Mike "Rikin" wrote: > Hi, > > I am running a macro where I copy and paste values from workbook to another. > Is it possible to fill in a cell with text without having to copying it fro...

If cell is Bold then 1, if cell is Italics then 2? #2
I have a list of hundreds of cells, the cells are either Bold, Italics, or Regular. I want those cells that are bold to result in 1 in another column. those in italics to will result in 2. Am I able to write a "if" function that achieve this? If not, what should I do? Please kindly advise ...

Macro to insert to move values of a cell to next cell in the same row
Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Hi There, Can anyone pls give the code for this query. Pls help ...

insert rows and copying cells in column b,c, and d into new rows
I currently have a spreadsheet that contains many rows of information exported from another application. In column a I have the same rows repeated through out which are five rows; starting at row 1 and ending at row 6, repeated at row 7 and ending at row 12. In column B I have data relating to the title in column A ,in colum C I have the title for the the data in D. Essentially what I need to do is the following: 1) copy the data in in column b every sixth row and pasting into the fifth row paste the data from the the second row colum c and d into column a and b into now bl...

Counting cells #3
Hi, I had this before, but it didn't work weel! I may have not made it clear. Range C5:C29 containes either letter C or letter M. Range D5:D29 containes either letter M or letter F line 5 C F line 6 C M line 7 M M ........... In cell D34 I want to count the number of occurance of the combination of C and M. The array formula =COUNTIF(C5:C29,"C") counts the value of colomn C. What adjustment do I need to do to include the values in colomn D? I tried this: =COUNTIF(C5:C29,"C"), (D5:D29,"M") but had an error Khalil Try one of these: =C...

Counting values based on another cell
Hi Column a1:a5 contains "BG" Column a6:a10 contains "GT" column a11:a15 contains "OTG" Column b1:b15 contains random numbers between 1 and 10 How can I count how many 1's, 2's, 3's etc appear in column b relating to "BG", then "GT" etc...? (ie BG contains two number 1's, one number 4 and two number 9's) and so on...) By the way, BG,GT and OTG can be in more rows other than a1:a5, a6:a10 etc...ie "BG" can be in more rows than a1:a5 Thanks in advance Try this =SUMPRODUCT((A1:A9="BG")*(B1:B9=1)...

individual cell protection
I wish to allow people to enter data etc into a cell, but once entered, that information can be deleted. Any help would be greatly appreciated. Thanks Bill Do you mean "but once entered information cannot be deleted"? -- Regards, Peo Sjoblom (No private emails please) "bill gillan" <bill gillan@discussions.microsoft.com> wrote in message news:A1C55347-C2D9-42A6-A205-D7C9E3EDA67B@microsoft.com... >I wish to allow people to enter data etc into a cell, but once entered, >that > information can be deleted. > > Any help would be greatly apprecia...

Date and Number cell formats
I am using Excel 2003 and I am trying to create a very basic weight chart by date. Column one is the date and column two is the weight. I would like all of the dates to appears as 01/06/2010, 10/05/2010, 11/15/2010, etc. -- in other words, mm/dd/yyyy with 2 digits for the month, 2 digits for the day, and 4 digits for the year. Instead, all I get for the date is: 1 or 2 digits for the month, depending on whether the month is a 2-digit or 1-digit number; and the same for the day, 1 or 2 digits. I want them all to appear as mm/dd/yyyy. I would like the weights to appear as 211.6 , 212.0...

Custom Highlighting/Fill Color cells
I am trying to create a custom color within the "Fill Colors" abilities in Excel 2000. I can do it on Word and PowerPoint when I go to the "Fill Colors" tab, then choose "More Colors" and I type in the color numbers I want (out of the entire rainbow selection) to create a custom color, BUT I can't seem to do this in Excel. Is this possible? Any help is GREATLY appreciated! Thanks! Look at tools/options/color tab, modify button Excel limits you to have 56 colors in any one workbook. "Los" <anonymous@discussions.microsoft.com> wrote in me...

Pasting Variable cell info into a macro
I'm trying to use a macro to cut and past a date from a cell (which will have various dates plugged into the cell) back into the macro directly. It works great the first run as long as the cell information doesn't change. Then of it doesn't self update the variable information from the cell on subsequent runs. Being a newbee, how can I write this macro so it picks up the date information from a specific cell so that when the macro is ran, it uses the latest info in that cell each time? See below for what I've got so far which won't update to new info from ...

Cell formatting doesn't display
I have Excel 2007. When I enter data in a cell, I can select it and highlight it, eg., in yellow. The highlight does not display, but it shows up on the Print preview as grey. Similar with the font color. If I select a color, eg, red, then type, the print displays in red - until I press "enter". The data displays black again. It also displays in print preview as black. I checked with my MS WORD. It seems to work OK. Any help is appreciated! What settings, etc should I check? thanks! Try turning off the high-contrast Accessibility Options. Check out the below l...

Pics in a cell
(office 2000 version) In which way can I paste Pics in a cell, I've already tried to copy, paste/ but they are not locked in the Cell. Even have I tried to Lock the cell, When I give the (cell) colums a search function, it trows all pics on each other . mine search function doesn't work. Who can help me? Milanvanbree@brabantia.com Cells can contain either values or formulas. Pictures and other objects exist on the drawing layer in front of the cells. Once you have the picture sized in front of a cell, you might try Format/Picture/Properties/Move and size with cells to keep the p...

blank cell conditionally formatted
I would like blank cells in my spreadsheet to be white (no shading). I would like the cells with text to be formatted to a selected color. I did a search in this NG but couldn't find any similar situations. Does anyone know how to do this? TIA One way: Format your cells as white (no shading). Select the cells to Conditionally format (with, say, cell A1 the active cell), and set the dropdowns and inputbox to read CF1: Formula is =LEN(A1)>0 Format1: <patterns>/<selected color> In article <1175008431.196607.19430@b75g2000hsg.googlegroups.com>, "da...

formatting cells depending on other cells
sorry for the weird topic, but i didn't know the english name for "bedingte formatierung"... however, the problem is the following: i'd like to highlight the text in one cell depending on the information in another cell. e.g. if the value in cell B2 is "female" the text in A2 should be green coloured and if the value is "male" it should become blue coloured. how can i do this? i don't get it... thanks in advance, malte Hi the translation is 'conditional format'. Try the following: - select cell A2 - goto the conditional format dialog ...

Merging Cells and Sorting
Is there a way to get around problems with sorting merged cells? I have a spreadsheet that contains some rows with merged cells, which doesn't allow me to sort the rows (ascending) using an autofilter. Is there some other way that I can split the cells so that I will be able to sort the rows? Or is there any way to lock 2 rows together so that they will be recognized as one? I would just input the data on a second line in the cell, but the cells have conditional formatting which I'd to keep. I'm wondering what other people are doing when they encounter this problem (a...

Deleting an Excel cell
Hi, I am writing a project in .NET which needs to update some cells. Now, these cells contain a function and the spreadsheet is linked. So, since you cannot update a cell which contains a function, I tried to delete it first (with the idea of updating later). But it does not even allow me to delete with the following statement: cmd.CommandText = "DELETE [Births$C24:C24] FROM [Births$]" cmd.ExecuteNonQuery() which should do the deletion if it works. But it is giving me an error on the cmd.ExecuteNonQuery() (i.e. when the actual delete SHOULD occur) telling me the following: "...

copying non adjancent cells
sorry Ms. debra dalagliesh I missed your reply to my query. I got it later through google groups search. thank you very much for clarifying my doubts. I am repeating your message for the sake of readers seeing only this messagae copy of Ms. Dalgleish's message from: Debra Dalgleish (dsd@contexturesXSPAM.com) Subject: Re: copying non adjacent cells If you want to copy multiple ranges, the selections must match in rows and columns In each column, the same rows must be selected, or nothing is selected In each row, the same columns must be selected, or nothing is selected For example, you...

Calculation within a Cell
I am trying to create cells that will make calculations for me. For example, I want to enter the number 140, and have the cell automatically make the calculation to 30 percent of that number. Any suggestions? Thanks. You would need macro for that Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Target.Value = Target.Value * 0.3 Application.EnableEvents = True End Sub will change whatever number is entered in A1 for information see http://www.mvps.org/dmcritchie/excel/event....

Find method skips over cells
This code works nine times and then skips Sept, Nov, and Dec I beleive this has something to do with merged cells, because those three months are merged cells, however it it works on March which is also a merged cell also merged cells. Hydra For MonthCounter = 1 To 12 Cells.Find(What:="$", After:=ActiveCell, LookIn:= _ xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate PriceAddress(Monthcounter) = ActiveCell.Address Next Monthcounter I'm not sure ex...