Relatively new to excel and I need help

[Silverman]

This is what I need.
I have data in let's say from A1:A200
I need that if I color which ever cell I choose from A1:A200, the cell
next to it displays a text of my choosing.
Example. If I select A5:A7 and color them Yellow, then B5:B7 will show
the letter R.
To get you in the picture, I need this so that when I'm doing a Bank
Reconsiliation, when I highlight the cells I need (by using the yellow
colour) in the cell next to it the letter R appears.
Thanks

 

[Don Guillett]

Sub getcolorindex()'Fire after all colors entered
On Error Resume Next
For Each c In Range("a1:a200")
Select Case c.Interior.ColorIndex
Case Is = 6: c.Offset(, 1) = "R"

'Case Is = ?: c.Offset(, 1) = "?"
'fill in other colors and letters

Case Else
End Select
Next
End Sub

 

[Luke M]

I'm afraid XL is not built to handle formatting that way. However, it can do
the reverse, where you mark cells B5:B7 with the letter R, and it can then
color A5:A7 yellow. Would this work?

To do this, select cells A1:A200. Make sure A1 is the active cell (the cell
you would currently be editing). Goto Format - conditional formatting. Change
the first dropdown box to "formula is". In the next box, input:
=B1="R"
Click the format box, goto pattern, and choose a yellow fill. Ok out of the
format dialogue.

Ok out of the conditional format dialogue. You should be set now.

 

[AdamV]

I'm with Luke, that's the way round I would do it (although Don's code
should work).

As an aside / extra note, you should remember that you can enter into
multiple cells at once, so for example select B5:B9, type "R" and rather
than just enter, press Ctrl-Enter and it will put that entry in all
those cells. This should not be any more work than colouring a bunch of
cells, in my view.

Hope this helps
Adam

 

[Silverman]

I'm with Luke, that's the way round I would do it (although Don's code
should work).

As an aside / extra note, you should remember that you can enter into
multiple cells at once, so for example select B5:B9, type "R" and rather
than just enter, press Ctrl-Enter and it will put that entry in all
those cells. This should not be any more work than colouring a bunch of
cells, in my view.

Hope this helps
Adam







- Show quoted text -

Thanks Don Luke and Adam for your help. I prefered Don's version
because that's what I was after.
I tried it and it work. It worked just the way I wanted. You're a
genius Don. I wish I increased my knowledge because I'm a total mess
expecially in VB.

With lots of appreciation - Silverman