Relatively new to excel and I need help

  • Thread starter Thread starter Silverman
  • Start date Start date
S

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
 
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
 
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.
 
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
 
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 -
Click to expand...

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
 
Back
Top