Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a list of 200 cells in Excel. I put in names and would like the text color in the cell or the background of the cell to change according to the name written. I use about 25 names.

share|improve this question
3  
use conditional formatting – Jook Oct 16 '12 at 12:37
Please be more clear so we can figure something out ... – t.thielemans Oct 16 '12 at 12:42
   
This is definitely not a programming question. Excel's built in conditional formatting feature is far better for this scenerio than any code you would write – psubsee2003 Oct 16 '12 at 12:44
@psubsee2003 Even with a 4 colour limit for conditional formatting? – pnuts Oct 17 '12 at 19:15
1  
@pnuts I do not have Excel 2007 on my machine to verify, but I seem to recall having more than 4 conditions and 4 different colors in Excel 2007 before, so I am skeptical of the article. – psubsee2003 Oct 17 '12 at 19:41
show 3 more comments

migrated from stackoverflow.com Oct 17 '12 at 4:59

2 Answers

up vote 2 down vote

I suggest that you use Excel's conditional formatting feature. You can look at help to figure out how to do this. You can also find various documented examples

Example: SU488776 example

share|improve this answer
up vote 2 down vote

Conditional formatting would be best for this, but if you'd like a VBA alternative you can record a macro in the developer tab and edit it under "modules" in the "project" sidebar. Then under ThisWorkbook in the Project sidebar put the code into a SheetChange event.

You need to change the two dropdown menus above the text area, the left one needs to be set to Workbook (only option typically), the right one needs to be SheetChange Then you can use this simple code for as many names as you like. Keep in mind there are many ways to do this.

You can put as many ElseIf's in between the if and else as you like.

Sub Macro

Dim i As Integer
i = 1

    Range("A" & i).Select

    If ActiveCell = "Name1" Then

        ActiveCell.Interior.ColorIndex = 12

    ElseIf ActiveCell = "Name2" Then

        ActiveCell.Interior.ColorIndex = 4

    ElseIf ActiveCell = "Name2" Then

        ActiveCell.Interior.ColorIndex = 11

    Else 

        ActiveCell.Interior.ColorIndex = 15

    End If

End Sub

using select in code is not the best programming practice, but this is a small amount of data so it is alright. Hope this helps!

share|improve this answer
2  
Better option would be to do this automatically by handling the worksheet change event so the colors will automatically update when the sheet is changed. – psubsee2003 Oct 16 '12 at 12:47
@psubsee2003 that is a good point. This code would be much more effective under the worksheet changed event – Mike Kellogg Oct 16 '12 at 12:49
Added some additional instructions on how to put code into a SheetChange event which will automatically run the included code when any change happens on the sheet. – Mike Kellogg Oct 16 '12 at 12:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.