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
979 Views

Similar Articles

[PageSpeed] 44

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:

Case 'Actual Duration' not getting updated in SQL
Knowledge Base article 913391: The "Actual Duration" field displays an incorrect value when you view the "Resolve Case" dialog box in Microsoft Dynamics CRM 3.0 This article corrects the time when you resolve a case by adding some script to an onsave event. Here is where it doesnt work: Lets say you go back and change the time from 2 hours to 4 hours on a Service Appointment. The form shows your updated time by running the onsave event but when you resolve the case, you original time displays and not the updated time. I looked in SQL and the ActualDuration and Sc...

Restrict HTML Formatted Mail
IS there a way to force all your incoming mail to be plain text? I do not want incoming mail to be in HTML format. I would like to restrict incoming mail or convert it to plain text once received (automatically). In Outlook 2003: Menu Tools/Options, Tab: Preferences, Button: Email Options, check: Read all standard mail in plain text. If you have different Outlook, check its help file. Hope this is useful to you. Let us know. rms JImL wrote: > IS there a way to force all your incoming mail to be > plain text? I do not want incoming mail to be in HTML > format. > ...

lookup link for customer address on account, case, address search
The way the address associations are handled today create tremendous gaps in the structure of CRM records compared to the structure of integrated backoffice financial systems records. What should be trivial integration tasks lead to a LOT of workarounds and customers frustration. Here is how we can improve. Abandon the idea of hardcoding first 2 address records on the account form. Add 2 address lookup fields on the account (relationships) that could be used to select any of the account addresses and expose them on the account form (still show individual address fields on the accoun...

Combo box list selection based on earlier selection by user
HI, I have a form that has a combo box 1 (Product) that reads a the Product table and has a unique id attached to each product (RO#). There is a second combo box 2 that is RO#. Now what I would like to do is when a user selects a Product in combo box 1, the system should lookup the Product selected from combo box 1 and upload the corresponding RO# in the combo box 2 list. Now I am using both combo boxes as Unbound and in the combo box 2 I have the two columns Product and RO# - in the Criteria row of RO# I have the following: Like IIf([Forms]![xa_f_lookup]![RO#]=" ",[Forms]![x...

Run report on multi selected records.
I try to make a report for a custom entity that works like the Account Summary: let's say that it's Customer's Inquiry Summary for entity Customer's Inquiry I made the report so that I'm able to run the report on Customer's Inquiry Form. But when I select multi records in the list of Customer's Inquiry and run report, only one record is show in one page of the the report. I would like to have the selected records appear in the report with each record on each page. How can I do this? ...

date formatting problem: x axis
I have several chart files that are all the same except for one that seems to go back to a formatting problem. I can't remember how I fixed it last time, which was temporary anyway. Problem is this: I am using mostly end of year dates (12/31/94, 12/31/95, etc.) However on my x axis it shows Dec-94, everytime I try to reformat it to the correct format(xx/xx/xx), I get 12/01/94, 12/01/95, etc. Always the first day of the month instead of the last. I'm not having these problems with all the others similar files. The only difference I have seen between the files is the &qu...

Conditional formatting on autoshapes
Dear experts, Jacob Skaria, MVP wrote me a marco below for the solution of conditional formatting on autoshapes. As Jacob's macro refers the value of A1 to the default name of the autoshapes, such as: the value of A1 = 2, then the autoshape named "Oval 2" will be changed to colour green. However, I want the value of A1 refers to the text inside Oval 2 instead, such as: value A1 = table and the text inside Oval 2 = table, then autoshpae Oval 2 will turn into colour green. How can I get it done? Please kindly advise. Thanks in advance. QUOTE Private Sub Wo...

Can't select objects in Excel 2007
I'm trying to create a form in Excel 2007, and although I can manipulate, copy & paste controls (check boxes, etc) I can't select multiple objects to align them, etc. I've checked on-line help (and have followed the instructions), and other websites, but no one seems to have indicated a problem. It is extremely cumbersome and I need a solution. Any ideas? Thanks in advance, Jim Berglund I know this is about a year too late, but it may help others who have stumbled across this page looking for similar help. On the Home tab, in the Editing group, click Find & Select....

Lot Selection not Showing when I select PO on Receiving Entry
Usually, the Lot selection screen would pop up when I select a PO at the Line Detail of the Receivings Entry, now it does not! And GP will let items post without a lot number! It seems as if a setting has changed. Any ideas anyone? Any help is appreciated. Thanks, R Are you sure the item is marked to track lots? This wiould be set item by item on the Item Maintenance Options window. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com &q...

Conditional formatting #96
Would it be possible to use conditional formatting for my problem below, if so could I please get some assistance how to construct the formula. For example, what I would like to achieve is that if data is entered in to cell C1, I would like all cells from C1 to J1 change to a different color,(all at once) and that color would remain in all those cells even when data is entered in to some of the other cells. In addition, if possible I woul like all cells to go back to normal when data is entered in the the final cell J1. It would be great if someone could find the solution to this. Man...

Select contact then hangs
Hi, please help. I have a user that when she selects a contact using To: in a new email, after selecting 2, it closes outlook??. Spec is W2kSP4\office2k with all windows updates and office updates installed. Profile has been deleted and re-created, plus i have set up personal folders and moved contacts into there, and set that folder as a default, but makes no difference. Please help. ...

passing dates in a conditional sum(if)
I have a workbook with 5 sheets. Sheet1 is Customers Sheet2 is YTD Sales 07 Sheet3 is Total Sales 06 Sheet4 is Total Sales 05 Sheet5 is Total Sales 04 Customer sheet has this basic formula in the columns for each year's sales based on the customer's number and the variable of the month that the sales was done. {=SUM(IF((Custnumber07=A3)*(Month07<=$J$1),amount07))} The problem is that the accounting program exports the invoices with the MMDDYY for each invoice. The $j$1 is the number of the month I limit it up to. I have a column in each sales sheet with the month() function to...

Lost row height format?
My boss is working in a workbook with numerous sheets. He saved the file and then reopened it after his HDD was replaced. When he reopened the file, all of the row heights were off a little bit, but since this is for a large financial report, it is a big deal. Does anyone know what could cause this and have any idea how to fix it. He swears he did not change any of the settings in excel, or manually adjust any of the rows. Prior to re-opening, all rows were set at 11. Now some are 12, some 11, some 11.5, some 10.??. This happened on every sheet. Help!!! Thanks, Mac Highl...

Selecting rows from various sheets
Hi all, I have several sheets with a few columns in each. One colum in each sheet has a unique value. Most of these unique values are th same in each sheet but not all. Is there any way that I can set up new sheet that will have the unique value in column 1 and th corresponding column values frommeach sheet beside it. For example Sheet 1 aa 1 1 1 bb 2 2 2 cc 3 3 3 Sheet 2 aa 4 4 cc 5 5 Result aa 1 1 1 4 4 bb 2 2 2 cc 3 3 3 5 5 Thanks alot, Sha -- sha ----------------------------------------------------------------------- shav's Profile: http...

add email to sub folder
Is there an easy way to add a received email address to a sub folder in outlook contacts. I have several individual folders set up under contacts, and the only way I have seen to be able to add an email address from a message received is to add it to the main contacts folder, and then copy it. Thanks for any assistance. Hi, Create a subfolder under contacts. Click onteh Contacts Folder. File --> New --> Folder. Under the New Folder window give a name for the subfolder and then Under the Next box which says "Folder Contains" Select Contact items. Under "Select w...

formatting time #2
How do I take off the seconds in my time-formatted cells? They look ok in the worksheet (h:mm) but in the formula bar and when I merge the data into WORD, the seconds show up. I also created a Custom format and that didn't work. Hillary You can format the merge field in Word. Right-click the field and after the field name type \"hr:mm" Regards Peter >-----Original Message----- >How do I take off the seconds in my time-formatted cells? >They look ok in the worksheet (h:mm) but in the formula >bar and when I merge the data into WORD, the seconds show >up. I...

Conditional Formatting #48
Excel 2003 SP2 Trying for format a cell based on the sum of a range in which the cell exists versus amount in another cell. So if the number in the cell changes, making the total of the cells exceeds a predetermined number, then the cell should be red. In verbal logic: if sum(a1:a10)>B1 then format A2 red. Can this be done through conditional formatting, or do i need to write a change-cell event code? Thanks. One way, using CF: Select A2: CF1: Formula is =SUM(A1:A10)>B1 Format1: <patterns>/<red> In article <uLlNOhaCHHA.1224@TK2MSFTNGP04.phx.gbl>,...

select text in Calendar
I'm working on a calendar in Publisher. I'm adding text into a cell by spacing down 1 point, then typing in 8 points. Doing fine until April. At that time, I can only select cells (whole days). It will not let me select the text. Help is no help. Any hints out there. Did I accidentally select something to do this? Bonnie Have you added a picture to the cell or a shape? If you have send it to the back, make the cell no fill and try again. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "GoBonnieGo" <GoB...

Number Formatting in XL
I am normally using the "Comma"-format with 0, 1 or 2 decimals. However, lately when I am trying to cut down from the default 2 decimals to 1 or 0, the number is showing up with two commas, and a number of 0s added after the last comma. One example; 10,00 becomes 10,0,000 when I try to reduce it to 10,0 Anyone with a cure for this disease? Hi what format have you applied exactly? -- Regards Frank Kabel Frankfurt, Germany "Zyvind" <Zyvind@discussions.microsoft.com> schrieb im Newsbeitrag news:EB775E31-21D0-4D17-AC7F-3030A5A2AF37@microsoft.com... > I am nor...

macro to filter data based on selected data
Hi, I am looking for a macro that requests input of a reference code and then a date and then filters the data in my spreadsheet based on this criteria. I am using excel 2007. Any help with this will be greatly appreciated. You can probably record a macro while doing it manually and then clean it up and add input boxes to ask the question or input your variable in a cell and use that. If all else fails, If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the ...

Link select with a blast
I have Win 7 Home Premium, x64. Quite often, when I select a link, a mouse pointer changes to a palm with a blast. I understand, the palm is the standard mouse pointer for link select, but what is the blast? What it indicates? -- Ilya Zeldes Fort Myers, Florida Hi Ilya, I suspect that this is being caused by a tracking program. Run something like Ad-Aware and see if it indicates a tracking company. If so, you can delete it, however, it might prevent you from accessing that particular website. Dave "Ilya Zeldes" wrote in message news:7A6FF9B1-5512-44...

Select Case in a Change Event
Hi, I am trying to use a worksheet Change Event to trap changes to specific columns. depending on which column is chnaged by the user, the value in another ciolumn will be altered; so, changes in either column O or P will update column Q, changes in either column T or U will update column V and changes in column Z or AA will update column AB. What I can't figure out is the Select Case bit (where I've got the shouted question below). Here's what I've put together, any comments would be much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) ...

Select Records based another table...
This may be very elementary, but i can't wrap my brain around it. I have tried to dummy it down as much as i can for myself, with plans of adapting it into something working. I have three tables that look like the following: Tbl 1: ProdID A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 Tbl 2: RmNum ProdID 1 3 1 2 2 1 2 2 Tbl 3: RmNum Style 1 A 2 C Relationships are: [tbl1 ProdID] ------ [tbl2 ProdID] [tbl2 RmNum] ...

display no seconds in time format [h]
Have used the [h] mm ss formatting to add up times over 24 hours but only want to display the hh mm and not the seconds (ss) the format option does not seem available, any ideas gratefully received. Hi Format>Cells>Number>Custom and in the white pane just type [h]:mm Regards Roger Govier Finolac wrote: >Have used the [h] mm ss formatting to add up times over 24 hours but only >want to display the hh mm and not the seconds (ss) the format option does not >seem available, any ideas gratefully received. > > ...

Multi-Select List Box Does Not Retain Selected Items
I created an Outlook Form in 2003. But when I select items in the list box they do not remain select when I select another control on the form. Any suggestions? Did you bind the list box to a keywords field? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "kaykay" <anonymous@discussions.microsoft.com> wrote in message news:276dd01c4638d$bd5af590$a501280a@phx.gbl... > I created an Outlook Form in 2003. But when I select > items i...