VBA Macro

Miami40 · #1 06-29-2018, 09:35 AM

Hello I am attempting to create a macro that will take the value of one cell and change the color of the cell and include text that represents the value. For example, if the value of the cell is "10" I want the macro to change the "10" to the word "Green" in white font and fill the background of the cell to Green. I need this for 4 different scenarios.

I have attached an example below

ArviLaanemets · #2 06-29-2018, 11:21 AM

I didn't understand, do you changed value display and cell/font color in cell with original value, or in another location. When in original location, then you can use conditional formatting (when in anothoer location, then really too - you have to add tha formula which reads the value from original cell.

So the basic case. Select the range with values, activate conditional formatting, and add a rule for every scenario (in my example I assume, that the values are in range A2:B10);

1st scenario (value = 0).
Create a new rule. Select "Use a formula to determine which cells to format" (this selection goes for all rules). Enter the formula
=(A2)=0)
Click <Format>, activate tab Number, select category Custom, and enter into Type field "blue". Activate tab Fill, and select blue color. Activate tab Font, and select white font color. <OK>. <OK>;

Repeat the same cycle for all 4 scenarios;

Check that Stop if True is unchecked for all rules. When checked, then uncheck;

<OK>. <OK>.

The cells are colored according your schema for all 4 shema values, and in standard colors for all other values. And those 4 numbers are displayed as color names instead of numbers.

NB! Really the values remain numbers, they only are displayed differently. So you can make any mathematical calculations with them like with other numbers.

NoSparks · #3 06-30-2018, 07:36 AM

Or...
to address the request as asked...

Code:

Sub Convert_Values_To_Colors()
    Dim rng As Range, cel As Range
    Dim celStr As String, celFontColor As Long, celFillColor As Long
    
On Error Resume Next
Set rng = Application.InputBox("With the mouse, select the range to work on.", "RANGE TO WORK WITH", Type:=8)
On Error GoTo 0
If Not rng Is Nothing Then
    For Each cel In rng
        Select Case cel.Value
            Case Is = 0
                celStr = "On-Hold"
                celFontColor = RGB(225, 225, 225)  'White
                celFillColor = RGB(0, 102, 204)    'Blue
            Case Is = 10
                celStr = "Green"
                celFontColor = RGB(225, 225, 225)  'White
                celFillColor = RGB(51, 153, 102)   'Green
            Case Is = 20
                celStr = "Amber"
                celFontColor = RGB(0, 0, 0)        'Black
                celFillColor = RGB(255, 255, 0)    'Yellow
            Case Is = 30
                celStr = "Red"
                celFontColor = RGB(225, 225, 225)  'White
                celFillColor = RGB(255, 0, 0)      'Red
        End Select
        
        With cel
            If cel.Value <> "" Then
                .Value = celStr
                .Font.Color = celFontColor
                .Interior.Color = celFillColor
            End If
        End With
    Next cel
End If
End Sub

Your picture doesn't seem to show that the cell colors are the basic red, green, yellow and blue.
Here's a little procedure that will give you the RGB color of a cell so you can adjust the RGB to match.

Code:

Sub GetRGBofCell()
    Dim R As Integer
    Dim G As Integer
    Dim B As Integer
    Dim RGB As Long

RGB = ActiveCell.Interior.Color
R = RGB And 255
G = RGB \ 256 And 255
B = RGB \ 256 ^ 2 And 255

MsgBox "RGB(" & R & ", " & G & ", " & B & ")"

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Footnote extraction macro [Why is this macro so slow? / anyway to make it faster?]	Le_Blanc	Word VBA	10	03-22-2021 11:38 AM
Spell check macro within macro button field doesn't work in one document	samuelle	Word VBA	0	07-20-2016 02:27 AM
Macro Question: Need help making a macro to highlight the first word in every sentence	LadyAna	Word	1	12-06-2014 10:39 PM
Macro Needed to bold specific lines and Macro to turn into CSV	anewteacher	Word VBA	1	05-28-2014 03:59 PM
custom icon, undo/redo for macro, permanent macro	Rapier	Excel	0	08-05-2013 06:30 AM