EXCEL: if A1= a word, then B1= colored cell

Which version of Excel? Because up until 2003, you can only have 3 conditional formats, plus the default conditional format, so that would handle 4 colors at best.
But in VB, it's possible, though.
--
Why complicate things to keep it simple when it's so easy to make it complicated?

Hello

To manage more than 3 conditions in 2003, you should try this program by Didier Fourgeot

It's certainly the most advanced method and quite easy to implement.
--

Always zen

Good evening,
Someone else wrote:

Sub Test()
'Variable declaration -------------------------
Dim Cel As Range
Dim F As Worksheet
'MEI ----------------------------
application.ScreenUpdating = False
'Screen refresh blocking
'Processing------------------------
For Each F In Worksheets
'For each sheet in the workbook
For Each Cel In F.UsedRange
'For each cell in the used range of the sheet
Select Case Cel.Value
'define case = cell value
Case "A"
Cel.Interior.ColorIndex = 3
Case "B"
Cel.Interior.ColorIndex = 41
Case "C"
Cel.Interior.ColorIndex = 4
Case "D"
Cel.Interior.ColorIndex = 6
Case Else
Cel.Interior.ColorIndex = xlNone
End Select
Next Cel
Next F
application.ScreenUpdating = True
End Sub

The macro is case-sensitive: "A" is recognized, but not "a". If you want it to work in both cases, you replace
Select Case Cel.Value
with
Select Case Ucase(Cel.Value)

If it suits you, we can trigger it automatically on the current sheet, either when changed, or when changing sheets.
Otherwise, please detail what you want to achieve
A+

-->Message edited by Gorfael on 20/07/2007 02:14:42<--

Good evening everyone,

I'm a bit late, but since I did it, I'm posting it anyway:
http://www.cijoint.fr/cjlink.php?file=cj200810/cijUlCe7FM.xls

the code for those who will read later:

Sub colorer() ' select the range receiving the color before calling the macro Const offsetCol As Integer = -1 ' control the cells located 1 column to the left Dim c1 As Range, c2 As Range For Each c1 In Selection For Each c2 In [Légende ] 'named range containing the searched text and color If c1.Offset(0, offsetCol).Value = c2.Value Then c1.Interior.ColorIndex = c2.Interior.ColorIndex Exit For End If Next c2 Next c1 End Sub

eric

Thank you for your responses, Eric's Excel link is great. I was able to complete my project thanks to you.

See you next time, have a good evening.

Well, what's blocking you with conditional formatting?

I would like a piece of code in VB though me ;):p

Actually, I'm working with Excel 2003 and I'm unable to set more than 3 conditions. I was wondering if it would be possible to use a formula with IF and OR.

By formulas, I'm sure it doesn't work... you can't change the color of a cell via a formula... but in VBA (by associating a VBA macro with the cell change), I know it's possible, but I haven't dug into the question since I don't use more than 3 conditions either ;) ... but I'm a programmer at heart, so:

LatelyGeek: if you have the solution ;)

thanks in advance :)

Thanks for me, I hope it solves your issue slamdunk77

If so, don't forget to put resolved at the top ;)

@++

Regarding macros, I don't know much because so far I've never needed this utility.

I just went to download the program, I will try it tomorrow.

In any case, thank you for your responses, and if others have other suggestions, don't hesitate!

Have a good evening.

I just saw your comment COCOTEHIER, I'll keep you updated tomorrow.

Thank you very much.

oki, thank you too

PS: I forgot to mention that if we set offsetCol to 0, we color the selected cells according to their content.
eric