Need help restructuring code tohighlight numerical data w/o special characters please

[estatefinds]

I have numerical data without special characters in column AG. this data will match data in the Range of AI2 through AP71.

I need the code "color cells in range or sheet" to be modified for this where the current data is located.
Also the current code looks for, for example 1-10-E, but it needs to be changed to look for just numerical data with out special characters or Alpha characters.

Also it currently colors the data it finds in the range AI2:AP71 to red.

is it possible to have each number to be colored a color specified for that number? the numbers are 1 to 7.

I had uploaded and example as well as in the example the colors assinged to these numbers.

Any help with this would be greatly appreciated.

Thank you very much in advance!!!

[Kenneth Hobs]

I don't know if you mean match AG to AI:AP in the same row or each AG value in all of AI to AP. To mark the colors, I don't know if you want a match and color just the found cell or the row AI4:AP4 for match to AG4.

I don't see any markup as red when I ran your macro. Maybe showing in a file the final result expected for just a few rows manually marked would be sufficient.

[p45cal]

Sub blah2()
Colours = Array(65535, 65280, 49407, 13995347, 6684927, 255, 6684774)
For Each cll In Range("AI2:AP71").Cells
  cll.Interior.Color = xlNone
  If IsNumeric(cll.Value) Then
    If cll.Value > 0 And cll.Value < 8 Then cll.Interior.Color = Colours(cll.Value - 1)
  End If
Next cll
End Sub

[estatefinds]

Ok so I have data in column AG this is just the occurence of the data in the column AE that consist of numbers 1 through 7. this is for me to review and not necessary for macro.
(the data that should be used is the data AU3 to AV9 that shows colors to be used to color data found in the AI2 to AP71.)

Now if you look at the data in AI2 through AP71 some you ll see in various spots single numbers through 1 to 7.

now I need macro in which I press ALT F8 and it will color the numbers 1 through 7 as each number are assinged a color as seen in the example. so for example the number 2 in AI 2 will be colored and any other found in the range of AI2 to AP71, and so on, the colors I would like assinged are in the range AU3to AV9.

Also the reason it didnt color red is because the current macro only recognizes the 1-10-E format and not the single number format for example "1" or "2" or "3" or "4" or "5" or "6" or "7". so for example the number "2" that is colored in cell AI2 and so should all the other ones as well in the range AI2 to AP71 if it is there.

so each number assigned a color, and when the macro is modified it will color the numbers found in the range AI2 to AP71 any of the 1 through 7. so all the ones that appear in the in the range will be colored the assinged color. all the of the 2's in the range found will be the color assigned that are found in the range, an so on.

When you open work book slide to the left of it and youll see the example of what origianl code id in regards to coloring red.

Thank you

[p45cal]

see adjusted code in msg#3

[estatefinds]

Yes exactly!!! Thank you very Much!!!!

You do Excellent Work!!!

[p45cal]

Or if you want to be able to change the colours, then adjust them in the range AV3:AV9 and run this:

Sub blah3()
For Each cll In Range("AI2:AP71").Cells
  cll.Interior.Color = xlNone
  If IsNumeric(cll.Value) Then
    If cll.Value > 0 And cll.Value < 8 Then cll.Interior.Color = Range("Av2").Offset(cll.Value).Interior.Color
  End If
Next cll
End Sub

[estatefinds]

Excellent!!! Thganks Again!!!

[estatefinds]

I ran into a problem when I deleted data in th range AI2 to AP71 And ran the so the numbers appeared and ran the the BLAH2 i got an error run time error?


I figured it out thanks Again!!!

[estatefinds]

Ok so I ran new data and ran the BLAH 2 and gpt an error message, can you help me with this? Im not sure why this is happening. I see the code when i open devoloper but is there something that is in the AI2 cell that im removing/ when i place new data?

wheni click Debug i get this

cll.Interior.Color = Colours(cll.Value - 1) Else

when i copy and paste the origianl data to run macro it works, but when I edit data by replacing with new data before runing the BLAH2 is get the run time error

[estatefinds]

ok, I think I figured it out! I dont change that data, i just use the other macros to run and the data will change then run BLAH 2. looks like all working well.
Thanks gain!

[p45cal]

This is a guess: you may have values >0 but less than 1?
Try changing to:

If cll.Value >= 1 And cll.Value <= 7 Then cll.Interior.Color = Colours(cll.Value - 1)

[estatefinds]

I'll give it a shot thanks again!!