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

 

[Gerald Higgins]

Might be possible with VBA, but not possible without VBA . . .

 

[Pauljj]

I thought that maybe the case, are you aware how this can be done in VBA ?

 

[Gerald Higgins]

Well I'd never done this before, but I cobbled this code together from something similar I'd done before.

This code looks at a small range of cells, some of which I had previously coloured a shade of blue (specified by color index 42), and then writes the word "BLUE" into the cells that are shaded blue

Sub Apply_text_based_on_colour()
    Dim Cell As Range
    Dim Rng1 As Range
    Range("G13:G25").Select
    Set Rng1 = Selection
    For Each Cell In Rng1
        Select Case Cell.Interior.ColorIndex
        Case 42
            Cell.Value = "BLUE"
        End Select
    Next
End Sub

 

[Gerald Higgins]

Don't forget that you can do this the other way round, using Conditional Formating.

For example, you can make the cell have a colour, based on the text that is in it (or that is in some other cell).

It sounds as if you are planning to go to each cell, apply a colour to it, and then get the text to be input automatically.
That's fine, but it might be just as easy to go to each cell, manually input the text to it, and then get the colour applied automatically through CF.

 

[MickG]

Something Similar !!

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Jobs
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Jobs = Array(vbGreen, "Jobs1", vbRed, "Job2", vbYellow, "Job3", vbBlue, "Job4", vbMagenta, "Job5", vbCyan, "Job6")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Jobs)
    [COLOR="Navy"]If[/COLOR] Range("A1").Interior.Color = Jobs(n) [COLOR="Navy"]Then[/COLOR]
        Range("c1") = Jobs(n + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Pauljj]

Hello Gerald, I think this is very similar to what I will need. Your code will add text to the cell that is formatted but what I will need is to format another cell. As example

A1 = Blue Then K1 = Job 1
A2= Yellow Then K2 = Job 4
A3 = Blue Then K3 = Job 1
A4 = Green Then K4 + job 5

B1 = Orange Then L1 = Job 3


and so on....does this make sense ?

 

[Gerald Higgins]

Maybe this ?

Sub Apply_text_based_on_colour()
    Dim Cell As Range
    Dim Rng1 As Range
    Range("G13:G25").Select
    Set Rng1 = Selection
    For Each Cell In Rng1
        Select Case Cell.Interior.ColorIndex
        Case 42
            Cell.Offset(rowoffset:=0, columnoffset:=10).Value = "BLUE"
           End Select
    Next
End Sub

Change the column offset value if it's not putting it in the right column.

 

[Pauljj]

Something Similar !!

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Jobs
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Jobs = Array(vbGreen, "Jobs1", vbRed, "Job2", vbYellow, "Job3", vbBlue, "Job4", vbMagenta, "Job5", vbCyan, "Job6")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Jobs)
    [COLOR="Navy"]If[/COLOR] Range("A1").Interior.Color = Jobs(n) [COLOR="Navy"]Then[/COLOR]
        Range("c1") = Jobs(n + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Perfect Mick, this is what I need, do you know if this will work across multiple cells. My range for colouring is C7:C50 with the jobs then appearing in M7: M50

 

[Gerald Higgins]

Obviously (well, perhaps not obviously ) you can adapt my code to deal with lots of colours at the same time...

Sub Apply_text_based_on_colour()
Dim Cell As Range
Dim Rng1 As Range
Range("G13:G25").Select
Set Rng1 = Selection
For Each Cell In Rng1
Select Case Cell.Interior.ColorIndex
Case 42
Cell.Offset(rowoffset:=0, columnoffset:=10).Value = "BLUE"
Case 43
Cell.Offset(rowoffset:=0, columnoffset:=10).Value = "other colour 1"
Case 87
Cell.Offset(rowoffset:=0, columnoffset:=10).Value = "other colour 2"
End Select
Next
End Sub