Colour text depending on offset value

[welshgasman]

Hi all,
The Community Car Scheme I volunteer for is wishing to print out the data from their spreadsheet, but want to keep the colour coded data.

I have found out how to colour code in mailmerge at Mailmerge Tips & Tricks

For that I thought of having a column for each actual colum that needs to be colour coded. Fortunately we are not talking about too many columns at the moment.

So if column A for a row needs to be Red, then the word Red is in column K.
So instead of the user actually colour coding the data, they just enter Red, Blue or Yellow in their respective columns and I write a little sub to set those colours (colourCode)
The benefit then is that I can test those extra columns in Mailmerge and set as needed.

So I have created two functions at present.
One is to set those columns where I can as the sheet will be quite long. The other is to get the colour of column A into column O. SO column K will set the colour of column A and column O will read that colour and display the colour word, hopefully the same as column K.?
Then when the 'colour' columns are correct, they can just set the colours via the colour columns, and run ColorCode.

However when the colour is Black, despite correctly identifying that colour, the relevent cell in column O is not set?, remains blank. I cannot find out why.

Not is mght be better to leave those empty so as to be able to see the wood for the trees, but I would like to find out why Black is not displayed?
Just working on one column pair for now, until I get it working as I would like.

TIA

Address trial.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Surname	Title	DataP	Address 1	Address 2	Tel 1	Tel 2	Ice	Notes		ColSurname	ColDataP
2	Alcock	Mrs	01/01/21	44 Einon Court	Gorseinon	01792 565656	07891 654565	Gina 565656			Yellow				Yellow
3	Able	Mr & Mrs	01/12/22	12 High Road	G/ Village	01792 565656	07891 654565
4	Ace	Mr & Mrs		30 Low Road	Gorseinon	01792 565656	07891 654565		Scooter		Green				Green
5	Ackroyd	Mrs	01/02/21	7 Heol Nant	Gorseinon	01792 565656	07891 654565Dau 548598				Blue				Blue
6	Bateman	Mr & Mrs		64 Bryngwastad	Gorseinon	01792 565656			Blind
7	Best	Mrs	01/12/19	22 Bryngwastad	Gorseinon		07891 654565
8	Blessed	Mrs		28 Highfield	Loughor	01792 565656			High Vehicle		Red				Red
9	Brain	Mr	01/06/18	6 High Street	Grovesend	01792 565656		Dau 548598	Folding Wheelchair
Sheet1

Cell Formulas
Range	Formula
O2:O9,K6:K9,K2:K3	K2	=showcolour("A" & ROW())

Option Explicit

Function ShowColour(RngCell As String) As String
Dim strColour As String
Dim iColour As Integer
iColour = Range(RngCell).Font.ColorIndex
'Debug.Print Range(RngCell).Font.Color
Select Case iColour
    Case 6
        strColour = "Yellow"
    Case 5
        strColour = "Blue"
    Case 4
        strColour = "Green"
    Case 3
        strColour = "Red"
    Case Else
        strColour = "Black"
End Select
ShowColour = strColour
   
End Function
Function GetColour(strColour As String) As Integer
Dim iColour As Integer
Select Case strColour
    Case "Yellow"
        iColour = 6
    Case "Blue"
        iColour = 5
    Case "Green"
        iColour = 4
    Case "Red"
        iColour = 3
    Case Else
        iColour = 1
End Select
GetColour = iColour
   
End Function

Option Explicit

Sub ColourCode()
Dim iLastRow As Integer, iRow As Integer, iColour As Integer
iLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
For iRow = 2 To iLastRow
    ' Surname first
    iColour = GetColour(Range("A" & iRow).Offset(0, 10).Value)
    If Range("A" & iRow).Font.ColorIndex <> iColour Then
        Range("A" & iRow).Font.ColorIndex = iColour
    End If

Next
End Sub

Edit: Marvellous, as soon as I upload here I get it working , however if anyone has a better method, I would love to hear it.

 

[Micron]

if anyone has a better method, I would love to hear it

Will take a look this afternoon as I'm curious about some things.

 

[Micron]

At first glance, some oddities wrt to data types, seemingly with no ill effects:

When A3 font is black, iColour value is -7 and its type is long, not Integer ( as you typed it: iColour = Range(RngCell).Font.ColorIndex )
?typename(activesheet.Range("A3").Font.colorindex)
Long

Seems to work as you posted for column K though, so I'm guessing the issue was that you're using the same function in both columns.
I might have had the user pick value list items in O if that's where you want them entering info. Then one sub could just concatenate the chosen word to "vb" and set colorIndex to vbRed, etc.

 

[welshgasman]

Hi Micron,
Strangely enough it started out as Long as I was trying to use Font.Color which is at least 5 digits, but could not equate a particular value to Red, Blue easily enough.
That is when I found the colorindex and a chart VBA: ColorIndex Codes List & RGB Colors and so decide to use those, and then I thought I could get away with Integer.

I've scrapped the Black option now, as it is easy to see any not coloured against those that are coloured.
I will be having a combo for column K eventually, just right now I am testing to see what I can do.

I am not using the same function in both columns?
K is set by colourcode, and column O is set by ShowColour as a simple check ATM.

As there will be a fair number of items (on other columns as well) coloured, I did not want the user (my controller) to have to add them all manually. Hopefully most will be populated, but if they pick a shade of green, then that could get missed.

I also tried the vb & Cell.Value, but that did not work. I believe as vbRed is just a constant for a certain value not a string?

This is all fairly doable now, especially as I managed to get Black to finally show, the decided that it was better not being displayed.

Thanks for the response.

 

[Micron]

I am not using the same function in both columns?

Mouse over a cell in each of the 2 columns in your posted data. I'm seeing the same function call: =showcolour

 

[welshgasman]

****, you are right

Some of the cells have that left over. My code puts the text into that column, but only if it is different to the actual colorindex in column A. Others have that formula left over.
It would have been corrected with ColourCode eventually as the actual sheet does not have those columns yet. So the first time it runs it would populate most of the column.