Select Case Conditional Formatting Sub?

I've tried to use the Sub below (Posted by Julie D I think) as a work around 
for more than three conditional formats and it works great IF I enter the 
data directly in the target cells.  But, if the taget range is populated by a 
formula the color changes do not occur.  Is there a way to modify this to 
work when the cells in the target range are formula driven?  (Or, maybe I'm 
doing something wrong, any ideas?)



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6:B10")) Is Nothing Then
With Target
Select Case .Value
Case 1: Target.Font.ColorIndex = 4
Case 2: Target.Font.ColorIndex = 3
Case 3: Target.Font.ColorIndex = 0
Case 4: Target.Font.ColorIndex = 6
Case 5: Target.Font.ColorIndex = 13
Case 6: Target.Font.ColorIndex = 46
Case 7: Target.Font.ColorIndex = 11
Case 8: Target.Font.ColorIndex = 7
Case 9: Target.Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

0
RCW (10)
2/5/2005 5:11:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1626 Views

Similar Articles

[PageSpeed] 3

Maybe...

Option Explicit
Private Sub Worksheet_Calculate()

    Dim myCell As Range
    Dim myRng As Range
    
    Set myRng = Me.Range("b6:b10")
    
    On Error Resume Next 'continue with next cell
    For Each myCell In myRng.Cells
        With myCell
            Select Case .Value
                Case 1: myCell.Font.ColorIndex = 4
                Case 2: myCell.Font.ColorIndex = 3
                Case 3: myCell.Font.ColorIndex = 0
                Case 4: myCell.Font.ColorIndex = 6
                Case 5: myCell.Font.ColorIndex = 13
                Case 6: myCell.Font.ColorIndex = 46
                Case 7: myCell.Font.ColorIndex = 11
                Case 8: myCell.Font.ColorIndex = 7
                Case 9: myCell.Font.ColorIndex = 55
            End Select
        End With
    Next myCell
    
    On Error GoTo 0

End Sub




RCW wrote:
> 
> I've tried to use the Sub below (Posted by Julie D I think) as a work around
> for more than three conditional formats and it works great IF I enter the
> data directly in the target cells.  But, if the taget range is populated by a
> formula the color changes do not occur.  Is there a way to modify this to
> work when the cells in the target range are formula driven?  (Or, maybe I'm
> doing something wrong, any ideas?)
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Range("B6:B10")) Is Nothing Then
> With Target
> Select Case .Value
> Case 1: Target.Font.ColorIndex = 4
> Case 2: Target.Font.ColorIndex = 3
> Case 3: Target.Font.ColorIndex = 0
> Case 4: Target.Font.ColorIndex = 6
> Case 5: Target.Font.ColorIndex = 13
> Case 6: Target.Font.ColorIndex = 46
> Case 7: Target.Font.ColorIndex = 11
> Case 8: Target.Font.ColorIndex = 7
> Case 9: Target.Font.ColorIndex = 55
> End Select
> End With
> End If
> 
> ws_exit:
> Application.EnableEvents = True
> 
> End Sub

-- 

Dave Peterson
0
ec357201 (5290)
2/5/2005 9:56:12 PM
Dave, your solution works great...THANKS!

"RCW" wrote:

> I've tried to use the Sub below (Posted by Julie D I think) as a work around 
> for more than three conditional formats and it works great IF I enter the 
> data directly in the target cells.  But, if the taget range is populated by a 
> formula the color changes do not occur.  Is there a way to modify this to 
> work when the cells in the target range are formula driven?  (Or, maybe I'm 
> doing something wrong, any ideas?)
> 
> 
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Range("B6:B10")) Is Nothing Then
> With Target
> Select Case .Value
> Case 1: Target.Font.ColorIndex = 4
> Case 2: Target.Font.ColorIndex = 3
> Case 3: Target.Font.ColorIndex = 0
> Case 4: Target.Font.ColorIndex = 6
> Case 5: Target.Font.ColorIndex = 13
> Case 6: Target.Font.ColorIndex = 46
> Case 7: Target.Font.ColorIndex = 11
> Case 8: Target.Font.ColorIndex = 7
> Case 9: Target.Font.ColorIndex = 55
> End Select
> End With
> End If
> 
> ws_exit:
> Application.EnableEvents = True
> 
> End Sub
> 
0
RCW (10)
2/5/2005 10:29:02 PM
Reply:

Similar Artilces:

Conditional Formatting Equation Question
Greetings, I am trying to get a Conditional Format to do something if there is "NO" entry in A2. Any ideas as to what this formula would look like? Any help would be appreciated. TIA -Minitman =A2="NO" or, if by "NO" entry, you mean a blank cell: =A2="" HTH Jason Atlanta, GA >-----Original Message----- >Greetings, > >I am trying to get a Conditional Format to do something if there is >"NO" entry in A2. > >Any ideas as to what this formula would look like? > >Any help would be appreciated. > >TIA >...

Set number format based on cell contents
I have a cell B3 which shows a Data Validation list of £ or % Having selected £ or % in B3 the user then enters a number in C3. I want to have cell C3 formatted to General if B3 = % and formatted to Currency if B3 = £. I'd really appreciate some help Thanks You need VBA for that. Paste the following macro into the sheet module for the sheet you are working on. To do that, right-click on the sheet tab, select View Code, and paste this macro into that module. Note that this macro does exactly what you asked. That is, it will change the format of C3 if you select $...

Using ExecuteQuery to get Listing of Cases
I'm attempting to programatically extract a listing of Cases (similar to the Cases listing in the Case Manager of the CRM app) using the Microsoft.Crm.Platform.Proxy.CRMQuery.ExecuteQuery() method. I can successfully extract Account and Contract info using ExecuteQuery, but I've been unsuccessful in locating a sample of a "QueryXML" string for listing Cases. This is my latest attempt, but I'm not even sure if "Incident" is the correct Entity for what I'm attempting. <fetch mapping='logical'> <entity name='Incident'> <order a...

Conditional import of data from other workbook or worksheet
Hey, What function would I want to use or how would a sample formula look like if I want to merge properties from two workbooks? ExcelFile1: full name and addresses ExcelFile2: full name and phone number Wanted result: ExcelFile1 with a new column containing the right phone number for each employee. Also, how do I reference the other file, or another sheet in the same workbook when building a formula? TIA BoB cell In excelfile1 C2, if names are in column A: =VLOOKUP(A2,[excelfile2.xls]Sheet1!$A$2:$B$4,2,FALSE) and fill down! Regards, Stefi „Bengt Bergholm” ezt írta: > He...

Maintain formatting of sub project when inserted into master proje
I'm using Project 2007. I have a master project and have inserted some sub-projects into the master project. ('Link to project' was checked which is what I want.) I had applied formatting to the original sub-projects' gantt charts task bars e.g. sub-project1 task bars were green, sub-project2 task bars were red etc. I found that this formatting was lost after inserting the sub-projects into the master. The sub-projects assumed the formatting settings of the master. Is there a way to maintain sub-project formatting? Thanks -- FionaDM In article <000A42E...

Selecting text in a document
Hi, I have a macro which finds a certain piece of text in the document and then moves down a line and then selects all the text between that and the end of the document and the selected text is later sent out in an email. This has been working for some time but I have recently had some problems because the text is not being selected as before (extendmode on?) and I am sending out blank emails. The code fragment that I use to select the text is as follows Selection.HomeKey wdStory Selection.Find.Execute "Issued at" Selection.MoveDo...

How to turn off automatic resizing of the view to fit selected obj
When I select objects that partially fall outsize the current window (becasue I have zoomed in on a particular detail), PPT automatically resizes my view. This makes drawing/editingn of small objects in a larger drawing tedious and time consuming. In past versions of PPT this did not happen. How can I turn-off this automatic resizing of the zoom factorview ? PPT 2007, right? Apply SP2 for Office 2007. That will fix it. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007.htm Fixing PowerPoint Annoyances http://tinyurl.com/...

Modify sub to exit if password is incorrect
How could the sub below be re-expressed the other way around, ie check the password, then exit sub immediately (with the error msg) if password is incorrect? Sub TestRest() Sheet8.Select Dim ThePassWord As String Dim t As String t = "What's the password" ThePassWord = InputBox(prompt:=t) If UCase(ThePassWord) = "BATTLEFIELD" Then Range("D10").Value = Range("G15").Value Else MsgBox "That is incorrect." End If End Sub Try this idea Sub passwordcheck() pw = "yourpas...

Conditional Number Sequencing
I am trying to automatically increment a column of numbers if a second column is populated, otherwise the auto-numbered cell will be empty if the cell defining it to be blank is also blank. As an example: Ticket # Ticket Invoice # Cost 7325689 Y 1234243 $ 48.78 1234244 $356.50 7325690 Y 1234245 $123.32 7325691 Y 1234246 $435.89 So, if the Ticket column is not populated, the Ticket # column will not increment. In the case of row 2 of data where row 2, column "Tic...

Control Form form a Sub Form
How do I activate a command button on the main form when I double click on the sub form? "TonyAntique" <u59027@uwe> wrote in message news:a5a6118d13dbf@uwe... > How do I activate a command button on the main form when I double click > on the sub form? The code would be like this: Me.Parent!Command99.Enabled = True But you'll need to define "double-click on the subform" more precisely. It's probably the dblClick event of the subform's Detail section you want to use. -- Allen Browne - Microsoft MVP. Perth, Western Australia T...

Invalid Sub-Total and Total in POP report
Hi, We've a user that came up with a POP Purchase Order Blank Form report (it's a customized version, but there was no changes since quite a while and has ever worked fine), on which the sub-total and the total is incorrect... On the screen, the POP shows 2 line items with a line value of 1305.00$ for the first and 1044.36$ on the second line. Both lines have had a receiption against it. When printing the report, the sub-total shows only the total for the 2nd line and the total line is @ 0$. I've ran a Purchase Order Documents reconcile, but this had no effect on the ...

Publisher mail merge date format
Hi When I mail merge dates from an excel spreadsheet in UK format the resultant date in the publisher field is in US date format. How do I get around this please? J Format the date field as text. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jayennbee" <Jayennbee@discussions.microsoft.com> wrote in message news:D3840909-4D93-434B-B0C1-5309EB6166E2@microsoft.com... > Hi > When I mail merge dates from an excel spreadsheet in UK format the resultant > date in the publisher field is in US date format. How...

How to generate conditional pop-up error message?
I have a moderately complex sheet with a lot of cells where the user (usually me) can enter data. I would like to add checks to certain cells and generate a pop-up warning message if there is an error. The pop-up would be like a Comment or an Input Message, except that it would appear if a test failed, rather than when hovering over the cell or selecting it. Have a look at Data | Data Validation in Help Then come back with more questions Example, you have arrange that a cell can have only values "cat" or "dog", and that a message (the text of which you get to decide) po...

how to convert time to decimal format
I need to convert 1 hour 38 min to decimals If cell A1 displays 1:30 , then the formula =A1*24 will display 1.5 when it if formatted general Likewise 1:38 gives 1.63 Does this answer the question? best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "yolly" <yolly@discussions.microsoft.com> wrote in message news:B116FEAB-FFE2-402C-A3F0-5061D819BEB1@microsoft.com... > I need to convert 1 hour 38 min to decimals EXCEL 2007 1. Put 01:38:00 into cell H 13 (or any cell you like). 2. Cell H 15 format as General (right cli...

Exporting auto format #2
I am exporting from Access to an Excel spreadsheet but I cant seem t stop the auto conversion that takes place. When I open the Exce spreadsheet the data is all different. E.g. I have a fund number o "8E0" and the cell shows 8 and not the correct data. It's fustratin for the datatype in Access is text and should stay the same in Excel The version I have in Access and Excel is 2002 SP-2 Any Ideas how t stop the auto conversion? I can do it by cut/paste of the query but am trying to export via the report output itself to Excel. Thank -- Message posted from http://www.ExcelForu...

2004 Word, Entourage, Excel "Help" text selections: how copy to Notes?
How does one highlight and then copy text from Help components of Office 2004's Word, Excel and Entourage? "Select all" and "copy" are grayed out and command-c and command-a do not work with "Help" text in any of the Office 2004 components. If there were some way to copy and paste these selections into Entourage Notes, one could do boolean searches for multiple criteria within "Help" notes. This also would allow one to add his or her notations to the "Help" passages copied into Notes. It would also make it possible to put the user&#...

Formatting window is gone
I'm using Office 2003 and until recently I had the option of selecting Plain Text, Rich Text, HTML from my toolbar via a box with a drop down arrow. Now that little box is gone. I can still change the format from the "format" button at the top toolbar, however I prefer having it as it was. Is there a way to return that option in my toolbar? -- Francine Sounds like you're no longer using WordMail as your editor. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://ww...

Conditional IF formula using multi-dimensional arrays
I am attempting to find a value in a 30 column (1000+ row) array that is conditioned on finding an exact value match in adjacent column and a not-to-exceed value in the same row in the 30 column array. If this were limited to single column arrays, I would not have a problem, but the issue arrises because I do not know which column the not-to-exceed value will be in so I must use the full 30 column array. Here is my formula, which I am hoping someone would be able to help me with: {=MAX(IF((BI3:BI5000=A1)*(BJ3:CM5000<=A2),BJ3:CM5000))} Thanks for your help, Your formu...

save formats of percentage when copying a chart
How do I keep the percentage format of data on a chart when I copy/move the chart to another spreadsheet that does not have the original data in it? ...

workflow send email template to both case contact and account
I am in a desperate search to find a solution for creating a workflow that sends an automated email notification upon Case open to both the contact that submitted the case and parent account's primary contact/email. It seems that i can only do this manually, or send to one or the other but not both. Any suggestions from another service provider that has tackled this issue? Perhaps I am not seeing a logical approach that already exists. thanks, Austin ...

conditional format #3
Fra: "Niels B�ge Egstrand" <nbenospam@djoef.dk> Emne: Re: Condition Formatting! Dato: 2. november 2004 11:55 is it possibel to opperate with 9 different collors in conditional format ? No, you get up to 3 formats under conditional formatting (plus the normal format). If you need more formats, you could use some event macro. "Niels B�ge Egstrand" wrote: > > Fra: "Niels B�ge Egstrand" <nbenospam@djoef.dk> > Emne: Re: Condition Formatting! > Dato: 2. november 2004 11:55 > > is it possibel to opperate with 9 different collors in...

Selecting / UnSelecting sheets
Hi, I have used this forum before to get help on my excel issues. Now I have come up with a situation that I cant get around (due to my limited knowledge). I will appreciate your help if someone can point out a way to select/unselect multiple sheets from a workbook using a macro.. a generic one that i can use from a custom toolbar.. thanx in advance. Cheers !!! Shuvro -- shuvro_basu How would that generic macro know which sheets to select (and which to skip)? Worksheets.select or sheets.select would get all of the worksheets (or all of the sheets), though. shuvro_basu wrote: > ...

Resolve Case Status Updates (CRM3.0)
I have found a couple decent suggestions that do not seem to work form me on updating the status reasons for resolving a case. Other than updating the forms "CASE STATUS" on the Case Form can I not update the Resolution status dialog that you get to from going to Actions < Resolve Case. This first post suggests the following that I DO NOT have, am I missing something? I have no Values Tab? 4. once you are in the properties box click on the Values tab Cited from: http://groups-beta.google.com/group/microsoft.public.crm/browse_thread/thread/519fdef1b915f18d/88082b58ad7903dc?lnk=g...

Great Plains should let you select the PO From Service Call Manage
Great Plains should let you select the PO to add to when creating a PO from the Inventory Requirements Window in Service Call Management ---------------- 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.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defa...

Complex Conditional Formatting
Hi all- I really need help with this one. I'm relatively new to conditional formatting but I have a pretty complex one that I need help on. I have a workbook with two worksheets in it. On sheet1, I have some data along with pie charts that are created with data from sheet2. What I need to do is have the pie chart area change color based on the data results in sheet2. Is this possible? Jeff Gross Yes - but best not to do it with conditional formatting. Use code to change the colours. Use the worksheet_change event, or assign a macro to a button. to trigger the code. Then ev...