More than 3 conditional formats?

[Ltat42a]

I made a spreadsheet for scheduling employees. I often grant leave
(vacation, sick, etc...etc...), and fill the open positions. I have
about 7 different variables. Conditional formatting works great, but
for only 3 of the 7 variables.

I'm looking to fill the cell color and change the font color when I
enter
certain text into the cell, including empty cells.

for instance...if a cell is empty - color =red
cell has "AL" - color=blue, text=white.
and so on....and so on - 7 different variables.

Is this possible?

 

[Bob Phillips]

You should be able to adapt this


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ltat42a]

You should be able to adapt this


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

in
message news:[email protected]...

Thanx Bob, that's a great start. In your example, when I input 3, I get
a blue cell with black text, when I enter either 6, 5, or 10, I get
nothing.

On my schedule, I have about 300 cells with text in it (i.e.
0800-1600).
When someone requests leave, I will remove that text, and, I'm wanting
that cell to shade red. This let's me know I have to fill that
position.
Now...in that same cell, I enter "SL", the cell will change to green
with white text. This will tell me that the person off is on sick
leave, and I have filled their position with someone else.

I can scroll down the schedule, look for red cells (no text in it),
then fill those positions and enter the type of leave that was used.

There will be 8 (not 7) different variables that I will be using. There
is also four different ranges of cells that I need this to work in
(B59; B2225; B3942; & B5661).

Hope this clarifies what I'm looking to do. Is this still possible??

Thanx...JF

 

[Bob Phillips]

Thanx Bob, that's a great start. In your example, when I input 3, I get
a blue cell with black text, when I enter either 6, 5, or 10, I get
nothing.

The example I gave only tests 1,2,3,4. You need to extend it.

On my schedule, I have about 300 cells with text in it (i.e.
0800-1600).
When someone requests leave, I will remove that text, and, I'm wanting
that cell to shade red. This let's me know I have to fill that
position.
Now...in that same cell, I enter "SL", the cell will change to green
with white text. This will tell me that the person off is on sick
leave, and I have filled their position with someone else.

I can scroll down the schedule, look for red cells (no text in it),
then fill those positions and enter the type of leave that was used.

There will be 8 (not 7) different variables that I will be using. There
is also four different ranges of cells that I need this to work in
(B59; B2225; B3942; & B5661).

Hope this clarifies what I'm looking to do. Is this still possible??

You will need to extend the cases, but no problem

Option Explicit

Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B59,B2225,B3942,B5661"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "": .Interior.ColorIndex = xlCIRed
Case "x": .Interior.ColorIndex = xlCIYellow
Case "y": .Interior.ColorIndex = xlCIBlue
Case "SL": .Interior.ColorIndex = xlCIGreen
.Font.ColorIndex = xlCIWhite
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Ltat42a]

Hey Bob...That's it! Just 1 more question.
In the VB code you posted, I added several of the variables I needed.
The shading works, but the font color does not, it returns a font color
of black, I need it to return a white font. Here's what I have -

Case "": .Interior.ColorIndex = xlCIRed
Case "AL": .Interior.ColorIndex = xlCIBlue
Case "SL": .Interior.ColorIndex = xlCIGreen
Case "ST": .Interior.ColorIndex = xlCIOrange
Case "AD": .Interior.ColorIndex = xlCIViolet
Case "CL": .Interior.ColorIndex = xlCIPink
Case "CT": .Interior.ColorIndex = xlCIIndigo
Case "VOT": .Interior.ColorIndex = xlCIBlack
Case "MOT": .Interior.ColorIndex = xlCIBrown

The "AL", "SL", & "MOT" return a value of white, the others return a
font color of black. I need them all white.

If in the future, I need to add or change these variables, what
considerations do I need?

Thanx again...Jim

 

[Bob Phillips]

I showed an example of how to add font colour as well. See the SL case in my
previous response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

You need to be explicit

Case "": .Interior.ColorIndex = xlCIRed
Case "AL": .Interior.ColorIndex = xlCIBlue
Case "SL": .Interior.ColorIndex = xlCIGreen
Case "ST": .Interior.ColorIndex = xlCIOrange
Case "AD": .Interior.ColorIndex = xlCIViolet
Case "CL": .Interior.ColorIndex = xlCIPink
Case "CT": .Interior.ColorIndex = xlCIIndigo
Case "VOT": .Interior.ColorIndex = xlCIBlack
Case "MOT": .Interior.ColorIndex = xlCIBrown
End Select
..Font.ColorIndex = xlCIWhite

In future, more conditions, just add another case.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ltat42a]

Hi Bob...that works great, just what I needed. Thank you.

I see one thing, if I enter a new employee and enter their shift, th
cell shade is white, and the text is white. Is the VB code preventing
cell default of white shade with black text? I can manually set thes
and it works, just inquiring.

Thanx...Jim

See the attached sampl

+-------------------------------------------------------------------
|Filename: schedsmpl2.zip
|Download: http://www.excelforum.com/attachment.php?postid=4182
+-------------------------------------------------------------------

 

[Bob Phillips]

Not that I can see Jim.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ltat42a]

Hey Bob, Thanx for your input - what a tremendous help!
I was given another suggestion on this spreadsheet. Without making any
alterations, all of the cells are shaded white with black text. As I
enter one of the 8 different variables, it shades the cell and the font
color is white.

I was asked if I could add the following, just not sure how to do it. I
did try and it didn't work. I need to add 5 more varibles. The only
change is, instead of shading the cell a certain color, I need to shade
the cells white with black text.

If I enter "A", "B", "C", "D", or "E", I need the cell shade to be
white, font color black.

Is it possible to add this in?

Thanx...Jim

 

[Bob Phillips]

Do you mean like this

Option Explicit

Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B59,B2225,B3942,B5661"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Font.ColorIndex = xlCIWhite
Select Case .Value
Case "": .Interior.ColorIndex = xlCIRed
Case "x": .Interior.ColorIndex = xlCIYellow
Case "y": .Interior.ColorIndex = xlCIBlue
Case "SL": .Interior.ColorIndex = xlCIGreen
Case "A", "B", "C", "D", "E":
.Interior.ColorIndex = xlCIWhite
.Font.ColorIndex = xlCIBlack
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ltat42a]

Do you mean like this

---SNIP---

Yes, that worked perfectly. I actually inserted the original 8
variables (all the color shading), then added what you posted above, it
works great.


Bob...Thanx for all your help...I really appreciate it.


Jim

 

[Bob Phillips]

Bob...Thanx for all your help...I really appreciate it.

It has been my pleasure Jim.