Examining alpha numeric data ?

[jmherbert]

Hello,

Newbie question here.

I am trying to make a worksheet change the fill color depending on the data in the cells. I have two issues I cannot find the answers to.

First off, here is an example logic I am trying to use:

For 
      Each c In Worksheets("Main").Range("L:L").Cells
               If c.Value = 123 Then c.Font.Color = vbRed
Next

End Sub

This logic works fine as stated, but I cannot get it to work with an alpha numeric entry, such as "EL010789"

Also, I cannot figure out how to change the fill color, so far I can only change the font color.

Thanks in advance,

James

[Ron Coderre]

No need for programming. Have you tried Conditional Formatting?

Select Col_L, with L1 as the active cell

Then..from the Excel Main Menu....
<format><conditional formatting>
Condition_1
Cell value is: Equal to: 123
Click the [Format] button and change the settings....Click [OK]
Click [OK]

That should do it....Each cell whose value is 123 will display in the Conditional Format you chose.

Does that help?
(Post back if you have more questions)

[jmherbert]

Thanks Ron!

The conditional formatting worked, but for my own sake (to further my skills with VB and excel) I would still like to know how to examine alpha-numeric data.

To clarify, the "123" I used to test the logic. For the application I am wanting this for, I would replace the "123" with the "EL001203" (or whatever)

I figured out how to do the color fill, but am still hung up on the alpha-numeric.

Sub Colors()
For Each c In Worksheets("Main").Range("A:Z").Cells
        If c.Value = 123 Then c.Interior.ColorIndex = 4
    Next

End Sub

Thanks again,

James

Edit;

For some reason the conditional formatting only worked for one condition. When I tried to add others, it would only work for the 1st one.

[Ron Coderre]

Since you've only ever made a few posts in this forum, let me help you present your problem better.

Post some sample data.
Describe what you want to do.
Post examples of what you tried (even if it didn't work)
Tell us what it does right and what it does wrong.
and...Let us know what errors you get.

-----------
Regarding your VBA code...
(see above)

and...Regarding Conditional Formatting....(also see above)
Meanwhile, see Debra Dalgleish's excellent CF instructions:
http://www.contextures.com/xlCondFormat01.html

[jmherbert]

OK,

Here is what I am trying to do. The application is a parts/assembly spreadsheet. The part #'s all begin with "EL", such as EL039939, EL023348, etc. with a total of about 25 different parts. The same part #'s show up multiple times within the worksheet What I would like to do is have it color code each of the part#'s automatically, wherever and whenever they show up.

I have tried several things, but this is the closest I have come to get it to work. Please note the 'c.Value = 123' is only for testing. (This logic works, if the cell contents are 123)

Sub Colors()
For Each c In Worksheets("Main").Range("A:Z").Cells
        If c.Value = 123 Then c.Interior.ColorIndex = 3
    Next

End Sub

What I want it to look like is something like this:

Sub Colors()
For Each c In Worksheets("Main").Range("A:Z").Cells
        If c.Value = EL00001 Then c.Interior.ColorIndex = 1
        If c.Value = EL00002 Then c.Interior.ColorIndex = 2
        If c.Value = EL00003 Then c.Interior.ColorIndex = 3
        If c.Value = EL00004 Then c.Interior.ColorIndex = 4
        If c.Value = EL00005 Then c.Interior.ColorIndex = 5
        If c.Value = EL00006 Then c.Interior.ColorIndex = 6
        If c.Value = EL00007 Then c.Interior.ColorIndex = 7
        '(and so on until all part #'s are covered)
    Next

End Sub

The problem seems to lie with the "c.Value = EL00001 part of the logic. This does not give any errors, it just doesn't do anything. The problem is the "EL", because if it is only numeric the logic (such as "c.Value = 1") it will work fine.

As far as the conditional formatting not working, I think I will try a reboot. It looks very straightforward, but it just isn't working for me.

Thanks

James

EDIT:

The conditional formating is still not working for me. I enter the info as shown, but get no effect from it.

[Ron Coderre]

See if this somewhat generic CF will work for you

Select A1:Z100, with A1 as the active cell

<format><conditional formatting>
Condition_1
Formula is: =AND(COUNTIF(A1,"EL??????"),ISNUMBER(--RIGHT(A1,6)))
Click the [format] button.....change the format...Click [OK]

That will color any cell with a value
that begins with "EL"
and is 8 characters long
and ends with 6 numbers

Does that help?...

or do you really need to look the values up from a table.
That can be done, too:
If the list is in AA1:AA25

Select A1:Z100, with A1 as the active cell

<format><conditional formatting>
Condition_1
Formula is: =SUM(COUNTIF(A1,$AA$1:$AA$25))>0
Click the [format] button.....change the format...Click [OK]

That will format any cell matching a listed item.