Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-21-2012, 12:11 PM
fedcco's Avatar
fedcco fedcco is offline Change cell color when selection is made from a drop down list Windows 7 32bit Change cell color when selection is made from a drop down list Office 2007
Novice
Change cell color when selection is made from a drop down list
 
Join Date: Aug 2012
Posts: 6
fedcco is on a distinguished road
Default Change cell color when selection is made from a drop down list

I’ve made an excel 2007 spreadsheet with some data validation drop down lists. I want to be able to, (either with VB code or the like) have the target cells and the fonts, change in color when a selection from the drop down list in Col. V is made. Below is a list of the drop down which is between cells V3:V18 of my spreadsheet. I want to be able to choose any cell in the range of D3-D350 and have that cells color change to the corresponding selection.




For example, if I choose "Blue" for cell D17, then that cell should change to Blue and font to white. If I choose cell D25 and select " PEDS CODE BLUE " again, the cell should turn Light Blue, font Black and so on.

Cell Selection Indx Clr Font Clr
V3 BLACK 1 2
V4 BLUE 5 2
V5 CADIAC ALERT 30 2
V6 GREEN 4 1
V7 GREY 16 1
V8 ICE ALERT 37 1
V9 ORANGE 46 1
V10 PEDS CODE BLUE 8 1
V11 PINK 7 2
V12 PURPLE 13 2
V13 RAPID RESPONSE 20 1
V14 RED 3 2
V15 STEMI ALERT 53 2
V16 STROKE ALERT 22 1
V17 WHITE 2 1
V18 YELLOW 6 1

The target cells are from D3-D350. Attachment Included.

Any help would be extremely appreciated.
Attached Files
File Type: xlsx CODE BOOK Sept 2012.xlsx (116.8 KB, 33 views)
Reply With Quote
  #2  
Old 08-21-2012, 09:39 PM
Venky62 Venky62 is offline Change cell color when selection is made from a drop down list Windows 7 64bit Change cell color when selection is made from a drop down list Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

Here is the sample macro. It uses a select case method to test for different inputs in the active cell.

You can add as many conditions as you want by adding more Cases.
For now I have added two cases:

Case is = "BLUE"
Case Is = "Black"

Add more of these statements before the "End Select" statement and then change the .Font.color number and the .Interior.color constants as per need. Since I do not know what exact font color and interior colors you want for different selections, I have left it to you to fill them up.

Enter this macro in the "This workbook" code window. To do this, Press Alt+F11 and in the Visual Basic window that opens, look in the Project Explorer window which is to the left side. Doubleclick on the "This Workbook" and enter this code on the blank window that opens on the righthand side.
Attached Files
File Type: txt SampleFontCellColor.txt (771 Bytes, 27 views)
Reply With Quote
  #3  
Old 08-22-2012, 06:18 AM
fedcco's Avatar
fedcco fedcco is offline Change cell color when selection is made from a drop down list Windows 7 32bit Change cell color when selection is made from a drop down list Office 2007
Novice
Change cell color when selection is made from a drop down list
 
Join Date: Aug 2012
Posts: 6
fedcco is on a distinguished road
Default

Thanks, Venky. I will have to try that. I don't know if you noticed, but I attached a copy of the spreadsheet, and in the post, I also included the cell numbers, the selection that would go to the target cells, the IndexColors numbers and Font color numbers that I'd be using.
Reply With Quote
  #4  
Old 08-22-2012, 10:19 AM
Catalin.B Catalin.B is offline Change cell color when selection is made from a drop down list Windows Vista Change cell color when selection is made from a drop down list Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Another approach to your problem is to work with arrays instead of select case (Note: All 3 arrays must have the same number of elements).
The problem is that selecting a value from a drop down list (or the result of a formula) does not trigger an event that can be intercepted by Worksheet change event, so the code below will not work until you double click the cell and exit with enter (editing a cell is a Workbook_Change event) , AFTER you select from list....

You might reconsider your work steps, and run a slightly modified code from a button after selecting all the entries, or using the Before_Close event to run the code. Place this code under Sheet module, works for column 4 (D), under row 400 (change this in code as needed).
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SearchArr, FontColorArr, InteriorColorArr As Variant, i As Integer

 If Target.Column = 4 And Target.Row < 400 Then
    With ActiveCell.Offset(-1, 0)

    SearchArr = Array("BLACK", "BLUE", "CADIAC ALERT", "GREEN", "GREY", "ICE ALERT", "ORANGE" _
       , "PEDS CODE BLUE", "PINK", "PURPLE", "RAPID RESPONSE", "RED", "STEMI ALERT", "STROKE ALERT" _
       , "WHITE", "YELLOW")
    FontColorArr = Array("2", "2", "2", "1", "1", "1", "1", "1" _
       , "2", "2", "1", "2", "2", "1", "1", "1")
    InteriorColorArr = Array("1", "5", "30", "4", "16", "37", "46", "8" _
       , "7", "13", "20", "3", "53", "22", "2", "6")
           For i = LBound(SearchArr) To UBound(SearchArr)

        If InStr(1, ActiveCell.Offset(-1, 0).Text, SearchArr(i), vbTextCompare) > 0 Then
        ActiveCell.Offset(-1, 0).Interior.ColorIndex = InteriorColorArr(i)
        ActiveCell.Offset(-1, 0).Font.ColorIndex = FontColorArr(i)
        End If
           Next i

    End With
 End If
End Sub
Reply With Quote
  #5  
Old 08-23-2012, 11:24 PM
Catalin.B Catalin.B is offline Change cell color when selection is made from a drop down list Windows Vista Change cell color when selection is made from a drop down list Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Here is the code, same that can be found in the workbook attached, which works with the event Workbook_BeforeClose.
The code will execute and change fonts and interior color on column D when you hit the close button on the workbook.
You have to complete the arrays, i noticed that you have in the workbook more values than in your first message. Remember that the arrays must have the same number of elements.
The code works on active sheet, so , if you want to run the code on another sheet, you just have to activate that sheet before closing the workbook.
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim SearchArr, FontColorArr, InteriorColorArr As Variant, i, Cell As Integer
    SearchArr = Array("BLACK", "BLUE", "CADIAC ALERT", "GREEN", "GRAY", "ICE ALERT", "ORANGE" _
       , "PEDS CODE BLUE", "PINK", "PURPLE", "RAPID RESPONSE", "RED", "STEMI ALERT", "STROKE ALERT" _
       , "WHITE", "YELLOW")
    FontColorArr = Array("2", "2", "2", "1", "1", "1", "1", "1" _
       , "2", "2", "1", "2", "2", "1", "1", "1")
    InteriorColorArr = Array("1", "5", "30", "4", "16", "37", "46", "8" _
       , "7", "13", "20", "3", "53", "22", "2", "6")
     Application.ScreenUpdating=False
For Cell = 3 To 350
           For i = LBound(SearchArr) To UBound(SearchArr)
              If InStr(1, ActiveSheet.Cells(Cell, "D").Text, SearchArr(i), vbTextCompare) > 0 Then
                ActiveSheet.Cells(Cell, "D").Interior.ColorIndex = InteriorColorArr(i)
                ActiveSheet.Cells(Cell, "D").Font.ColorIndex = FontColorArr(i)
              End If
           Next i
   Next Cell
    Application.ScreenUpdating=True
End Sub
Attached Files
File Type: xlsm Copy of CODE BOOK Sept 2012.xlsm (125.9 KB, 30 views)

Last edited by Catalin.B; 08-24-2012 at 01:10 AM.
Reply With Quote
  #6  
Old 08-25-2012, 12:09 PM
fedcco's Avatar
fedcco fedcco is offline Change cell color when selection is made from a drop down list Windows 7 32bit Change cell color when selection is made from a drop down list Office 2007
Novice
Change cell color when selection is made from a drop down list
 
Join Date: Aug 2012
Posts: 6
fedcco is on a distinguished road
Default

Catalin, I got the spreadsheet and see how it works. By the way, great job. Now, "HELLLLP", how do I get to where the formula or array is??? I've looked at the view code, went to D400 and still no luck.
Reply With Quote
  #7  
Old 08-26-2012, 11:09 PM
Catalin.B Catalin.B is offline Change cell color when selection is made from a drop down list Windows Vista Change cell color when selection is made from a drop down list Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Press Alt+F11, this will open the Visual Basic Editor. On the left side of this window, you will see the VBA Project with your file name in brackets, expand the project, double click the ThisWorkbook module; in the right side you will see the code presented above. Here you will have to edit this code to complete the arrays.
Reply With Quote
  #8  
Old 08-27-2012, 02:27 PM
fedcco's Avatar
fedcco fedcco is offline Change cell color when selection is made from a drop down list Windows 7 32bit Change cell color when selection is made from a drop down list Office 2007
Novice
Change cell color when selection is made from a drop down list
 
Join Date: Aug 2012
Posts: 6
fedcco is on a distinguished road
Default

Catalin.B, u da MI just need one more tweak to the code. When I delete the info in the target cells, the colors remain in the cells. Is there some way of making the cells turn back to white? Your input would be much appreciated.

Thanking you in adavance,
Fred R.
Reply With Quote
  #9  
Old 08-27-2012, 02:28 PM
fedcco's Avatar
fedcco fedcco is offline Change cell color when selection is made from a drop down list Windows 7 32bit Change cell color when selection is made from a drop down list Office 2007
Novice
Change cell color when selection is made from a drop down list
 
Join Date: Aug 2012
Posts: 6
fedcco is on a distinguished road
Default

Catalin.B, u da MI just need one more tweak to the code. When I delete the info in the target cells, the colors remain in the cells. Is there some way of making the cells turn back to white? Your input would be much appreciated.

Thanking you in adavance,
Fred R.
Reply With Quote
  #10  
Old 08-27-2012, 10:49 PM
Catalin.B Catalin.B is offline Change cell color when selection is made from a drop down list Windows Vista Change cell color when selection is made from a drop down list Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Insert this line of code under line: For Cell= 3 to 350
Code:
If Len(ActiveSheet.Cells(Cell, "D")) = 0 Then ActiveSheet.Cells(Cell, "D").Interior.ColorIndex = 2: ActiveSheet.Cells(Cell, "D").Font.ColorIndex = 1
Tip: When you press the close button to run the code and save the workbook, after the code runs, you may choose from the save dialog to cancel closing, so the code runs but the workbook is still open...
Reply With Quote
  #11  
Old 08-28-2012, 07:30 AM
Catalin.B Catalin.B is offline Change cell color when selection is made from a drop down list Windows Vista Change cell color when selection is made from a drop down list Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

I have to admit that i was wrong when i said that a change event does not respond to a Data Validation change ; the code will work every time you make a selection in column D or you clear the cell; the code you needed is presented below and must be pasted in Sheet 1 module. Sorry, my mistake !

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
        
        Set rng = Target.Parent.Range("D1:D350")
             
            If Target.Count > 1 Then Exit Sub
            
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            
            With Target

    SearchArr = Array("BLACK", "BLUE", "CADIAC ALERT", "GREEN", "GREY", "ICE ALERT", "ORANGE" _
       , "PEDS CODE BLUE", "PINK", "PURPLE", "RAPID RESPONSE", "RED", "STEMI ALERT", "STROKE ALERT" _
       , "WHITE", "YELLOW")
    FontColorArr = Array("2", "2", "2", "1", "1", "1", "1", "1" _
       , "2", "2", "1", "2", "2", "1", "1", "1")
    InteriorColorArr = Array("1", "5", "30", "4", "16", "37", "46", "8" _
       , "7", "13", "20", "3", "53", "22", "2", "6")
           For i = LBound(SearchArr) To UBound(SearchArr)
If Len(Target) = 0 Then Target.Interior.ColorIndex = 2: Target.Font.ColorIndex = 1
        If InStr(1, Target.Text, SearchArr(i), vbTextCompare) > 0 Then
        .Interior.ColorIndex = InteriorColorArr(i)
        .Font.ColorIndex = FontColorArr(i)
        End If
           Next i

    End With
End Sub
Reply With Quote
  #12  
Old 08-28-2012, 06:45 PM
fedcco's Avatar
fedcco fedcco is offline Change cell color when selection is made from a drop down list Windows 7 32bit Change cell color when selection is made from a drop down list Office 2007
Novice
Change cell color when selection is made from a drop down list
 
Join Date: Aug 2012
Posts: 6
fedcco is on a distinguished road
Default

Kooooollll. Now that we've gotten over this hurdle, how can I get the cells to turn white again when they're cleared. Can I use the string that you last sent me; if so where in the code, if not, what can be done? Never mind, I thought that I'd be able to select several cells and clear them at once. But 1 at a time will work. Catalin, much thanks for your attention and input. I know that it's not a lot, but I really do appreciate all you've done...

Last edited by fedcco; 08-28-2012 at 07:17 PM. Reason: Reiterate
Reply With Quote
  #13  
Old 08-28-2012, 10:43 PM
Catalin.B Catalin.B is offline Change cell color when selection is made from a drop down list Windows Vista Change cell color when selection is made from a drop down list Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

It is already in the code, just read it...Of course, works for 1 cell, as this line says:
If Target.Count > 1 Then Exit Sub
If the target cell is cleared, the line of code below, which is already in the code above, will reset the cell color settings.
If Len(Target) = 0 Then Target.Interior.ColorIndex = 2: Target.Font.ColorIndex = 1
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell color when selection is made from a drop down list Macro ? - Checkbox in Word + drop down selection help dice1976 Word 4 07-24-2012 11:18 AM
Change cell color everytime a value is selected in dropdown list angelica_gloria Excel 4 01-27-2012 06:47 PM
Made a mistake with a macro change lance_kidd Word 0 02-09-2011 06:36 PM
Ho to perform multi selection in drop down lists? nashville Word 0 09-29-2010 07:10 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead Learner7 Excel 0 07-08-2010 05:50 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:21 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft