Conditional formatting macro

Help.


I would like to know if it is possible to have a macro that changes
the colour of a cell dependant on the number or text within.

eg
If the following cells were populated as follows

a1=1 Make the cell Green

    =2 Make the cell Red

    =3 Make the cell Blue

    =4 Make the cell Orange and so on till 7

Is this at all possible ?
I know that i can colour cells 3 times with simple conditional
formatting, but would like to run upto seven different colours.

any help would be appreciated.

Steve

0
4/27/2007 12:29:35 PM
excel 39879 articles. 2 followers. Follow

1 Replies
615 Views

Similar Articles

[PageSpeed] 8

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"    '<=== change to suit

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
        With Target
            Select Case .Value
                Case 1: .Interior.ColorIndex = 10   'green
                Case 2: .Interior.ColorIndex = 3    'red
                Case 3: .Interior.ColorIndex = 5    'blue
                Case 4: .Interior.ColorIndex = 6    'yellow
'etc.
            End Select
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"K1KKKA" <instructorf@hotmail.com> wrote in message 
news:1177676975.301644.150820@t38g2000prd.googlegroups.com...
> Help.
>
>
> I would like to know if it is possible to have a macro that changes
> the colour of a cell dependant on the number or text within.
>
> eg
> If the following cells were populated as follows
>
> a1=1 Make the cell Green
>
>    =2 Make the cell Red
>
>    =3 Make the cell Blue
>
>    =4 Make the cell Orange and so on till 7
>
> Is this at all possible ?
> I know that i can colour cells 3 times with simple conditional
> formatting, but would like to run upto seven different colours.
>
> any help would be appreciated.
>
> Steve
> 


0
bob.NGs1 (1661)
4/27/2007 1:57:50 PM
Reply:

Similar Artilces:

Shortcut to format painter
Is ther a keyboard shortcut to the format painter and if not what is the code line i should use to create my own shortcut plaese Judith : A few links on keyboard shortcuts, et al. Chip Pearson http://www.cpearson.com/excel/ShortCuts.htm David McRitchie http://www.mvps.org/dmcritchie/excel/shortx2k.htm I've never seen a shortcut for the Format Painter. David McRitchie's site has a link to a macro that provides some functionality. Search the page for 'Painter'. HTH Paul ------------------------------------------------------------------------------------------------------...

sending attachments in rich text format
This is a multi-part message in MIME format. ------=_NextPart_000_007C_01CA9538.F7528CD0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit Can anybody tell me how I send an attachment with an e-mail from within Microsoft Office 2007 in rich text format please Wendy ------=_NextPart_000_007C_01CA9538.F7528CD0 Content-Type: image/gif; name="QMARK.GIF" Content-Transfer-Encoding: base64 Content-ID: <7D394D5C2EB043D9A973E27399E2B9B5@andyPC> R0lGODlhEwAfAPcAAP8ICP8PDv8VFf8dHP8kI/...

Macro Question Again
I'v never had any dealings w/ macro before, so this is all new to me. I have a windows 98 computer that is running office 2000 and there is an excel macro on this pc that was created by someone years ago. I have to get this 98 machine of the network and replace it w/ a 2000/XP platform also running either office 2000/xp. So far i'v copied the macro and am able to run it, but its not gathering the correct info. It is collecting the correct info on the 98 machine, just not the one im replacing it w/. It looks like some of the fields are supposed to be populated w/ info in a text d...

Macro looping?
How do I code a macro so that it loops until it goes through all of the information on the excel file? This way I do not have to rewrite the same code for 20 lines one day and then 30 lines the next day. sub loopit() lastrow=cells(rows.count,"a").end(xlup).row for i= 1 to lastrow cells(i,1)=?? next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "klh84" <klh84@discussions.microsoft.com> wrote in message news:7E2B2C07-1D80-4D28-9D05-4BA024BF8669@microsoft.com... > How do I code a macro so that it loops...

Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any given month and in the sheet I have an individual's time reported for each day of the week. I have a second workbook that contains an individual's time reported by each day for the entire month. I would like to compare the two to determine if there is a mismatch and highlight those cells. The logic goes something like this: (1) I need to match person A in column C of workbook1 to the same name in column C in workbook2. (2) I then need to match the date of the month on workbook 1 & 2 for person A in step #1. (3)...

Attachments convert to text format
When I send a message with an attachment in Outlook XP some receivers claim that all they receive is a text mail. My default mail format is plain text. If I change it to HTML I fear some recivers would not see the content at all. How can I solve it ? Note: I change the format of a particular mail if it contains an attachment, but sometimes I miss to do that. Tork2001 wrote: > When I send a message with an attachment in Outlook XP some receivers claim > that all they receive is a text mail. > My default mail format is plain text. If I change it to HTML I fear some > recivers ...

Outlook macro question
I have two email accounts in Outlook 2003. I know about the Accounts drop list in new emails. But is it possible to create a few macros so that I end up with two buttons where each runs a macro that starts a new email using a specific account? One button would start a new email using account 1 and the other button would start a new email using account 2. If so, could someone please give me some pointers? Thanks. Outlook doesn't provide any direct way to change the account for an = outgoing message in versions before Outlook 2003. See=20 http://www.outlookcode.com/codedetail.aspx?id=3D88...

Figures locked in text format
I imported data from a web page and used the feature that allows me to post the info to Excel with the destination formatting of the file I am posting it to. However, when I try to manipulate the data, as I have done in the past with other data, it will not let me reformat the text to number- no matter what I do to it. I've tried everything! I want to be able to sum the column, etc. Give us an example of the data you are working with. And what have you tried to "do to it"? Regards, Fred "Marcia" <Marcia@discussions.microsoft.com> wrote in ...

Excel limited cell formats
I have run into Excel's limit on 4000 cell foremats. I have since split the excel workbook into two separate workbooks. My question is, how can I launch these from a template and have the two workbooks link with each other, My original workbook has 25 separate sheets within the workbook for a total of 3.25mb size. I have a whole slew of macros assigned to different command buttons. Mike Johnson ...

Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental changes during update by different users. However, if I protect them, any new row creation and deletion are disable. Is there any way resolve it? Especially, when create a row, how can all formatting and formula to create automatically for the new row? Thanks, Scott What version of Excel? "Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message news:%23bsg6kCBGHA.4092@TK2MSFTNGP09.phx.gbl... >I need to protect the formatting & formula in each cell to avoid accidental >changes d...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffici...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

XSLT, XPath, and XSL Formatting Objects (XSLFO)
does dot net support apis for XSLFO in addition to XSLT? Thanks In data Tue, 13 Jul 2004 11:47:40 -0500, David Laub ha scritto: > does dot net support apis for XSLFO in addition to XSLT? AFAIK no, you have to use nfop: http://nfop.sourceforge.net/ -- Lawrence "In IE we trust" Lawrence Oluyede wrote: >>does dot net support apis for XSLFO in addition to XSLT? > > > AFAIK no, you have to use nfop: > http://nfop.sourceforge.net/ You can, but you don't have to. There are plenty of production quality XSL-FO formatters supporting .NET. Most of them ar...

conditional running sum
Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields: Date Adbn% 01/01/09 (Mon) 5% 01/02/09 (Tues) 6% 01/03/09 (Wed) 9% 01/04/09 (Thurs) 6% 01/05/09 (Fri) 8% I need it to end up with the following: Date Abdn% 01/01/09 5% 01/02/09 5.5% Avg of Mon&Tues 01/03/09 6.6% Avg Mon...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

HELP! Record Macro in TOOLS > MACRO is gone!
I was in Customize function trying to enable the Stop Recording toolbar and now in TOOLS > MACRO the Record Macro option is gone. I'm working on a project and I need this back. Can someone help? I'm using Excel 2003. Thanks, Joe M. Try this: In the customize window > click the Options tab > push the reset toolbars & menus button. Exit excel and re-load it Micky "Joe M." wrote: > I was in Customize function trying to enable the Stop Recording toolbar and > now in TOOLS > MACRO the Record Macro option is gone. I'm working on a ...

macro warning
I have a workbook that consists of 15 worksheets. One for each month and then a summary page for the year plus a couple of worksheets containing charts. Every time I open it I'm prompted that the workbook contains macros. I haven't created any macros. I go to tools>macros and bring up the window no macros are shown. Anybody have any ideas why this is happening? Not really a show stopper but rather irritating. gls858 One possibility is that you need to delete any empty modules that may have housed macros. Hit ALT+F11 and this will open the VBE (Visual Basic Editor) Top left you wi...

macro-import another xls file
Hi, i need a macro to import another xls file.When i run macro i need a window to appear and to browse to the file i need to import. I allways open another file, so i want to browse for the file. Can this be done? Thanks! Try some code like the following: Sub AAA() Dim FName As Variant Dim Filter As String ' Excel 2003 workbooks Filter = "Excel Files (*.xls),*.xls" ' Excel 2007 workbooks workbooks Filter = "Excel Files (*.xlsx;*.xlsm;*.xlsb),*.xlsx;*.xlsm;*.xlsb" ' Excel 2003 and 2007 Filter = "Excel Files (*.xls;*.xlsx;*.xlsm;*.xlsb),*...

Excel, Macro designed to send mails via Outlook : question on certificate
Hi everybody, I created in Excel a macro designed to send specific mails via Outlook. Because an Outlook dialog box asked me all the time whether I authorize the transmission or not, I created a certificate using MS Office "SelfCert". So far so good. My macro is signed. However, Outlook keeps on asking me everytime to authorize the transmission of the mails. What should I do / check ? Anybody ? Many thanks in adavance for your time and kind help. Daniel Everything you wanted to know about sending emails fro Excel using Outlook. http://www.rondebruin.nl/sendmail.htm Go...

Change the format of my footer {date} option?
I would like to show the date in my footer as January 25, 2005 instead of the default 01/25/05 Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub put this in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "Tori" <Tori@discussions.microsoft.com> wrote in message news:817363F1-32AB-4528-B1D6-304F8E712EA8@microsoft.com... > I would like to show the date in my footer as January 25, 2005 instead of the &g...

Cell formatting: displaying lat/long coordinates
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Rather than formatting a cell to display time in hours and minutes and = seconds, I would like to display latitude and longitude in degrees, = minutes, and seconds. e.g. 43=BA 25' 34" Is this possible in Excel 2000? ------=_NextPart_000_0008_01C4366C.F3FDF050 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W...

Macros within Excel
Is it possible to do a macro to update information between the following situation. 1 - Journal Spreadsheet - same every week - needs to be updated in cells J2, J5, J8, etc. - every 3rd cell with information from spreadsheet 2. 2 - Payroll Spreadsheet - different every week - information in column H - cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to J5, H15 to J8, etc. When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a different name because of the date i.e. Payroll 2-15-08 - my macro only works from Spreadsheet 1 to Spreadsheet 2 - when...

I'm looking for a cookbook page format
Final copy will be bound in 6 x 9 inch format (book size). Thanks :) 2 recipes per page I'm thinking. ...

Executing a macro from a cell
1. There is some way to make that excel, starting from a conditional structure in a cell, execute automatically a macro??? Something like this: A B 1 2 1 =if(A2=1,macro1(),macro2()) No, functions can return a value, they cannot change the format or run a macro. You can use event code to do it, but not a worksheet function. -- HTH RP (remove nothere from the email address if mailing direct) "filo666" <filo666@discussions.microsoft.com> wrote in message news:122D7DF6-CF7B-4AFB-A6F7-5F55DA090A86@microsoft.com... > 1. There is some way to make that excel, starting ...