Can specific text entries change color without Conditional Formatting?

Hello everyone. I'm having a bit of a problem with color formatting in Excel 2003 lately.

I did a search for this topic, but all I found were references to "searching" for cells colored a certain way, or sum/average/locate cells which were colored. I couldn't find anything that said something about changing the actual text itself based on a set of criteria.

My situation is this...

In cell A1, I have a drop-down list from Data Validation. When a specific selection is made in the list, cell B1 pops-up with a text entry based on a VLOOKUP which checks cell A1. For simplicity sake, I will use simple letters for these text entries. For example, if the 1st item in the drop-down is selected, the letter "A" will appear in cell B1 and I need it to be RED. If I pick the 2nd item in the drop-down, I need the letter "B" to appear and be BLUE. This continues for 6 different entries, all of which being a different text and color at the same time. If I go back and change the drop-down selection, I need the color of that text to change accordingly. I've actually got close to 30 consecutive rows worth of Drop-Down lists, each one corresponding to cell in the next column. I could really care less what the colors actually are, as long as they are readable on a white background. In fact, if the font colors themselves are the problem, I'll even go with changing the background at this point.

I've tried Conditional Formatting already. The 3 limit doesn't allow me to do what I need to do, which is to have 6 different colors used for the text that is inside the cell. I'm aware of the Format > Cells > Number Tab for Custom selections, but that only seems to work for text in general, not for specific text. Since the entries in B1 are not numbers, I can't use the built-in [Red] and [Blue] etc. for the custom formats. If anyone knows of a way I can specify a color based on specific text entries, please let me know. Perhaps a combination of Conditional Formatting 3 of those colors and something else entirely different can solve the problem.

I already have my lookup function in cell B1, but if there is some other color-based function that I can add to it...that would be great. Something like: IF(A1=1,"Text is Red",IF(A1=2,"Text is Blue"... all the way to 6. I know I can fit up to 7 IF statements, so that wouldn't be a problem, but I haven't found anything anywhere which has this.

Basically, I need a way to automatically change the text 1 of 6 colors based on the specific text entry. I would be more than happy to explain this situation in further detail, but I think I've gone on far enough as it is. Thanks to anyone who can offer assistance.

Sincerely,
Darqphire

Have you considered using an Event macro?

Select the tab for the worksheet, right-click on the tab, from the context menu select "View Code". Paste this where you see the cursor flashing:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column <> 1 Then Exit Sub
    If Target.Row > 6 Then Exit Sub
    
    Select Case Target.Text
        Case "A"
            Target.Offset(0, 1).Font.ColorIndex = 32
        Case "B"
            Target.Offset(0, 1).Font.Color = vbRed
        Case "C"
            Target.Offset(0, 1).Font.Color = RGB(100, 200, 300)
        Case "D"
            Target.Offset(0, 1).Font.ColorIndex = 3
        Case "E"
            Target.Offset(0, 1).Font.Color = vbBlue
        Case "F"
            Target.Offset(0, 1).Font.Color = vbCyan
    End Select

End Sub

These are not, in fact, 6 different colors. I was trying to demonstrate the 3 different ways I know of to specify font color. For Excel, ColorIndex is probably the best.

I have this set up only for the first 6 rows of column A. So, you will want to change that as well.

Wow that was fast! I've seen something similar to this in my google searches, but since I'm not much of a programmer, I was confused with some of the lingo. I'll try adapting your idea to my worksheet and I'll let you know how it goes. Thanks a lot.
--- Darqphire

Now that I know you like the idea, here is something that will fit your workbook better, I think.

The original was based on "A", "B", "C" ... , and required you to hard code the column number and row number(s) it should act on.

The revised is based on position in the validation list, and will act on a change to any cell that has a validation list on the worksheet (and not any other cell in the worksheet).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim matchRow As Long

    'apply this routine ONLY to cells that have validation lists
    On Error GoTo leave
    If Target.Validation.Type <> 3 Then Exit Sub
    
    'attempt to find match with validation list
    Set rng = Range(Target.Validation.Formula1)
    On Error Resume Next
    matchRow = 0
    matchRow = Application.WorksheetFunction.Match(Target.Value, rng, 0)
    If Err Or matchRow = 0 Then
        'no match ?!?; set font color to black and exit
        Err.Clear
        Target.Offset(0, 1).Font.ColorIndex = 1 'Black
        GoTo leave
    End If

    'we found a match, set font color in next column
    On Error GoTo leave
    Select Case matchRow
        Case 1
            Target.Offset(0, 1).Font.ColorIndex = 3     'Red
        Case 2
            Target.Offset(0, 1).Font.ColorIndex = 10    'green
        Case 3
            Target.Offset(0, 1).Font.ColorIndex = 13    'violet
        Case 4
            Target.Offset(0, 1).Font.ColorIndex = 53    'Brown
        Case 5
            Target.Offset(0, 1).Font.ColorIndex = 5     'Blue
        Case 6
            Target.Offset(0, 1).Font.ColorIndex = 46    'Orange
    End Select
    
leave:
    If Err Then Err.Clear
End Sub

Really? I suppose that would be more convenient. When you say "any" data validation, do you mean any data validation with the same settings or literally ANY data validation. I know I have to "apply the routine", but I wasn't sure exactly how to apply them to some and not others. The reason I ask is because I have multiple drop-downs all throughout the worksheet, some of which are on the same page, but have different source material. I have quite a few dependent drop-downs that I'm using, based on Named Ranges.

Now that I see that the code is really in-depth, maybe I should show you what I've really got going on. It didn't occur to me earlier to refer to Cell A1 as text and not a number. I noticed in your first example (which I think I follow pretty well) that it said "If Target.Column <> 1 Then Exit Sub" which I'm assuming means that the DV selection had to be 1. I can't imagine changing that to text would be very difficult.

In columns A:N, I have a merged cell which has the drop-down list of about 340 choices (on average). I'm not sure if those cells being merged changes things at all? But the selection on the row right beneath it is dependent on what is selected above it. It drops down to about 290 (on average). For about 30 rows, this A:N is selectable for those amount of choices from the drop-down, but dependent upon previous selections. Well anyway, I wasn't sure if the Offset part of the code would have to change based on the merged cells; does VBA count A:N as one cell or 14?

One more thing I wanted to tell you was this...I can't believe that you guessed the EXACT colors I was thinking of in your second example! You must have been reading my mind or something LOL. I think I need to invest in something that can teach me the finer points of VBA vocabulary though, being that your second example seems more complex. Don't get me wrong though, it looks intriguing. I'll have to spend some serious time on it very soon. I just wish I was more familiar and well versed in the whole thing.

I'll give your second idea a try next, probably tomorrow sometime. I didn't expect a response so soon! I'm dying to find out if it's going to work for me.

Once again, thanks a lot.

First, let me explain "Target", because having a firm grip on that is essential to answering a few of your questions. This routine runs whenever you make a change to a cell on the worksheet (but NOT when a cell value changes due to a formula in the cell). "Target" is the range that was changed; thiscould be a single cell or a large range of cells depending on what the user changed. In the case where what they changed is a selection from a drop-down validation list, this is a single cell.

When you say "any" data validation, do you mean any data validation with the same settings or literally ANY data validation. I know I have to "apply the routine", but I wasn't sure exactly how to apply them to some and not others. The reason I ask is because I have multiple drop-downs all throughout the worksheet, some of which are on the same page, but have different source material. I have quite a few dependent drop-downs that I'm using, based on Named Ranges.

Obviously, we do not want this routine to run for EVERY change that is made on the worksheet. We only want it to change when a user has made a change in one of the drop-down validation selections. In the first example I posted, I had validation lists in cells A1:A6. So, if the "Target" column was not 1 (Excel uses column Numbers internally and only displays letters because that is what most people seem to prefer), then we exited the routine. Similarly, if the Target row was greater than 6, we exited the routine.

In the revised version, I thought the above was too limiting. So, instead, I check the validation of the Target. If it has no validation, or if the validation is not a list (validation type 3), then we exit the routine.

If you want the routine to run for SOME but NOT ALL of your list-validated cells, then you might want some combination of these logic in the first example and the second example.

"If Target.Column <> 1 Then Exit Sub" which I'm assuming means that the DV selection had to be 1

Maybe. It did not mean that they selected the first thing on the list. It meant that the CELL they made the selection in was in Column A (column number 1).

I'm not sure if those cells being merged changes things at all?

The way it is written right now, it probably does. Because your initial explanation was that the dependent cell was in column B. So, right now, the code will format the cell one column to the right of the cell with the DV.

the selection on the row right beneath it is dependent on what is selected above it

So, we need to change the offset from (0,1) to (1,0). Offset(0,1) means "one column to the right in the same row"; Offset(1,0) means "one row down in the same column".

wasn't sure if the Offset part of the code would have to change based on the merged cells

will need to change it for sure. At least because we want to format one row down vs. one column to the right.

does VBA count A:N as one cell or 14?

Both. If you put this code in your workbook (either in a Module or in the same place the Event code is now) and run it, you will see that the first message box tells you that selecting cell A1 selects 14 columns, but offsetting one column from A1 puts you in O1. Weird, huh? But, if you think about it, it makes sense.

Sub testme()
    Range("A1").Select
    MsgBox Range("A1").Address, vbOKOnly, Selection.Columns.Count
    MsgBox Range("A1").Offset(0, 1).Address
End Sub

I can't believe that you guessed the EXACT colors I was thinking of in your second example!

There just are not that many good colors. I was running out of choices.

your second example seems more complex

There really are only 2 major differences. One is deciding whether to run the routine or not. That was explained above (but, if not explained well enough, holler and I'll give 'er another go). The other is using the MATCH function instead of typing in exact text for all of the possible selections.

Indeed, as you now admit that the lists depend on previous selections, there would be NO WAY to make this code work without using something like MATCH. (And, by the way, I thought about the possibility of using Named Ranges for the List formula. If the Name works for the List, it should work for this routine.)

Since you obviously know how to use VLOOKUP, then you will understand MATCH. It is a close cousin. You can use MATCH in a workbook to find which row contains the MATCH that VLOOKUP finds. The input variables to MATCH are: what you are looking for (text or number), the range you expect to find it, and exact match or not. So, using MATCH with the final input "0" is the same as VLOOKUP with the final input FALSE.

matchRow = Application.WorksheetFunction.Match(Target.Value, rng, 0)

I'll check back to see if there are further questions.

Once again, thanks a lot.

Nada. The reason I lurk here so often is because I never woulda dreamed up some of the stuff people ask about. I learn every day from this forum. It's pretty amazing.