HELP:Color code values with function in excell

[tns5852]

Hi,

I have values that I want to color code in my table. Originally I was tagging them as numbers, but instead I need them as colors.


Function conversion(pVal As Double)

If pVal > 0.05 Then
conversion = "1"
ElseIf pVal > 0.001 Then
conversion = "2"
ElseIf pVal > 0.0001 Then
conversion = "3"
ElseIf pVal > 0.00001 Then
conversion = "4"

End If

End Function



Now I want instead of conversion = "1" etc to be

Function conversion(pVal As Double)

If pVal > 0.05 Then
(make white)
ElseIf pVal > 0.001 Then
(make brighter blue)
ElseIf pVal > 0.0001 Then
(make green)
ElseIf pVal > 0.00001 Then
(make navy blue)

End If

End Function

I want the text and cell the same color (number and cell so the number disappears so that I just have a colored cell but the value is still there if I want to look at it).


Please help me! I am confused as to how to do this

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[wigi]

I am confused as to how to do this

That's not so surprising, since that's not possible with functions...

You need at least a procedure (Sub ...() ) to color the cells.

 

[Oliver Houghton]

Have you tried using conditional formating?

Is this data in worksheet cells?

If so, you could just use the drop down menu option Format>Conditional Formating.

This allows you to specify up to 3 conditional formats based on cell values. Therefore, if you initially format all the cells to be white with white text, then set up colour formating for "1", "2" and "3", the cells should automatically update.

 

[tns5852]

I want to preserve the values of the cells instead of changing them to numbers (I want them tagged as colors intead). If I write a sub, I reference the range of cells and include these if statements about what values I am checking? I need to first check the value of the cell for a range of cells (columns E -AU (90 rows) and color the values
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[wigi]

For instance:

Sub DoColouring()

    Dim r As Range
    For Each r In Range("E1:AU90")
        Select Case pVal
            Case Is > 0.05: Call Coloring(r, 2)
            Case Is > 0.001: Call Coloring(r, 37)
            Case Is > 0.0001: Call Coloring(r, 4)
            Case Is > 0.00001: Call Coloring(r, 11)
        Next
    Next
    
End Sub

Function Coloring(rng As Range, iColorNumber As Single)
    rng.Interior.ColorIndex = iColorNumber
    rng.Font.ColorIndex = iColorNumber
End Function

Wigi

 

[tns5852]

For instance:

Sub DoColouring()

    Dim r As Range
    For Each r In Range("E1:AU90")
        Select Case pVal
            Case Is > 0.05: Call Coloring(r, 2)
            Case Is > 0.001: Call Coloring(r, 37)
            Case Is > 0.0001: Call Coloring(r, 4)
            Case Is > 0.00001: Call Coloring(r, 11)
        Next
    Next
    
End Sub

Function Coloring(rng As Range, iColorNumber As Single)
    rng.Interior.ColorIndex = iColorNumber
    rng.Font.ColorIndex = iColorNumber
End Function

Wigi

Thanks wigi, but I am getting a compile error "Next without for"
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[tns5852]

I tried end but am still getting compiling errors
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[tns5852]

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[wigi]

The first "Next" should be changed to "End Select".

 

[tns5852]

The first "Next" should be changed to "End Select".

yes I tried that a few mintues ago actually, and it compiled. When I tried running it, however, nothing happened
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">