EXCEL: if A1= a word, then B1= colored cell
Solved
slamdunk77
Posted messages
13
Status
Membre
-
slamdunk77 Posted messages 13 Status Membre -
slamdunk77 Posted messages 13 Status Membre -
Hello,
I'm reaching out to you because I'm having some formula issues with Excel 2003.
Here is my problem: in my column A, I can find 6 possibilities (TB, TJ, TV, AP, ART8, PGTRX) and I would like to color the cell in column B.
For example: if A1=TB then B1= BLUE cell or if A1=TJ then B1= YELLOW cell
A2=TV then B2= GREEN cell or if A2=ART8 then B2= PURPLE cell
A3=AP then B3= RED cell
And so on for more than 200 rows...
I've tried to manage it with conditional formatting, but without much success! Due to lack of knowledge.
So if someone could guide me on this or provide other solutions, I would be very grateful.
Thank you very much, have a nice day
I'm reaching out to you because I'm having some formula issues with Excel 2003.
Here is my problem: in my column A, I can find 6 possibilities (TB, TJ, TV, AP, ART8, PGTRX) and I would like to color the cell in column B.
For example: if A1=TB then B1= BLUE cell or if A1=TJ then B1= YELLOW cell
A2=TV then B2= GREEN cell or if A2=ART8 then B2= PURPLE cell
A3=AP then B3= RED cell
And so on for more than 200 rows...
I've tried to manage it with conditional formatting, but without much success! Due to lack of knowledge.
So if someone could guide me on this or provide other solutions, I would be very grateful.
Thank you very much, have a nice day
Configuration: Windows XP Internet Explorer 7.0
14 réponses
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?
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
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<--
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:
eric
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.
See you next time, have a good evening.
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 :)
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 ;)
@++
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 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.