Excel 2003, triggering a result from a colour ?

[Pauljj]

An early stage question but if I for example format A1 Green, is it possible that, from a pre-definded list of colours I can say Green = Job A, so the word Job A appears as a text value in for example C1

...and If I formatted A1 Yellow and Yellow meant Job C, that Job C appeared in C1 ?

Many thanks
Paul

 

[Pauljj]

It seems like you have a working solution now, from Mick, which is great, but my code (as quoted in post #18) works for me - it inserts "Urgent Phone" in M10, if C10 is red, and so on . . .

Gerald, yes I have it working with Micks code but please accept my gratitude for all the time and effort in coming back to me with my constant queries. its really appreciated

 

[Pauljj]

Mick, sorry one more question. If I remove the colour, so there is no fill pattern at all, how can I then remove the text ?

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] MG18May06
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Jobs(1 To 10, 1 To 2)
'[COLOR=green][B] alter the "10" (Above) to the number of jobs[/B][/COLOR]
'[COLOR=green][B]& add to array (below) with proper Job Names and[/B][/COLOR]
'[COLOR=green][B]your chosen colour index.[/B][/COLOR]
Jobs(1, 1) = "Jobs1": Jobs(1, 2) = 5 '[COLOR=green][B]Alter last number (color Index)as Req'ed from Color Code[/B][/COLOR]
Jobs(2, 1) = "Jobs2": Jobs(2, 2) = 3
Jobs(3, 1) = "Jobs3": Jobs(3, 2) = 6
Jobs(4, 1) = "Jobs4": Jobs(4, 2) = 4
Jobs(5, 1) = "Jobs5": Jobs(5, 2) = 35
Jobs(6, 1) = "Jobs6": Jobs(6, 2) = 6
Jobs(7, 1) = "Jobs7": Jobs(7, 2) = 8
Jobs(8, 1) = "Jobs8": Jobs(8, 2) = 44
Jobs(9, 1) = "Jobs9": Jobs(9, 2) = 7
Jobs(10, 1) = "Jobs10": Jobs(10, 2) = 2
[COLOR=navy]Set[/COLOR] Rng = Range("C7:C50")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]For[/COLOR] n = 1 To UBound(Jobs)
        [COLOR=navy]If[/COLOR] Dn.Interior.ColorIndex = Jobs(n, 2) [COLOR=navy]Then[/COLOR]
            Dn.Offset(, 10) = Jobs(n, 1)
        [COLOR=navy]ElseIf[/COLOR] Dn.Interior.ColorIndex = xlNone [COLOR=navy]Then[/COLOR]
            Dn.Offset(, 10) = vbNullString
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Pauljj]

Unfortunatley Mick, it didn't do anything, it didn't do anything wrong it just still left the text in place after I formatted the cell back to 'no fill'

 

[MickG]

Select a cell with "No Fill" (no colour), Run the code below.
Use the number it gives you to replace the constant "XlNone" in the last bit of code.

MsgBox Selection.Interior.ColorIndex

Regards Mick

 

[Pauljj]

That worked a treat, thank you very much, I switched it to -4142 and it now works.