VBA: Specific number in a serie-of-numbers in a cell equals a color

Infine

Board Regular
Joined
Oct 16, 2019
Messages
93
Office Version
  1. 365
Platform
  1. Windows
Hello,

If I have numbers in a cell "1294392393", I want the 4th from the right side (the thousand number), I want a "color/border" on the whole columns.

For instance:

"1294392393" = 12943[9]393 ----- the nr "9" shall make a color blue on the whole column.
In a row below the "12943" = 1[2]943 ----- The nr "2" shall make a color orange on the whole column.

"1000" = [1]000 ---- The nr "1" shall have the color Red on the whole column.

And last example:

"14751565152512" = 1475156515[2]512 ---- the nr 2 shall have orange on the whole column.



I found this code in this forum from someone else topic:

Code:
Sub Controll_datas()
On Error Resume Next

Dim c As Range

For Each c In Range("C2", Range("C" & Rows.Count).End(xlUp))
    If IsNumeric(Mid(c, 3, 1)) Then
        c = "TGMS"
    End If
Next c

End Sub

This takes ANY number, I want specific number, and instead of c= "TGMS" I want to make for each nr a color.
So every number:
1 = blue
2 = orange
3 = etc etc. until 9.


How should I code this? I can't figure out because I want specific number, not any... I want to If IsNumeric(Right(c, 4, 1) = 1) Then for nr 1. but this doesn't work...
I know coding on PHP, so excel coding is a bit different where I need to relearn some.
 
Change this
Code:
            If IsNumeric(Right(Left(Cl, Len(Cl) - 3), 1)) Then
                Intersect(Cl.EntireRow, Range("A:D")).Interior.ColorIndex = Choose(Right(Left(Cl, Len(Cl) - 3), 1) + 1, 3, 5, 9, 11, 15, 23, 35, 44, 56, 49)
            Else
to
Code:
            If IsNumeric(Right(Left(Cl, Len(Cl) - 3), 1)) Then
                If Right(Left(Cl, Len(Cl) - 3), 1) = 1 Then Intersect(Cl.EntireRow, Range("A:D")).Interior.ColorIndex = 3
            Else
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top