software

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 quot;ALquot; - color=blue, text=white.
and so on....and so on - 7 different variables.

Is this possible?--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=497871You should be able to adapt thisPrivate Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = quot;H1:H10quot;

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)

quot;Ltat42aquot; gt; wrote in
message ...
gt;
gt; I made a spreadsheet for scheduling employees. I often grant leave
gt; (vacation, sick, etc...etc...), and fill the open positions. I have
gt; about 7 different variables. Conditional formatting works great, but
gt; for only 3 of the 7 variables.
gt;
gt; I'm looking to fill the cell color and change the font color when I
gt; enter
gt; certain text into the cell, including empty cells.
gt;
gt; for instance...if a cell is empty - color =red
gt; cell has quot;ALquot; - color=blue, text=white.
gt; and so on....and so on - 7 different variables.
gt;
gt; Is this possible?
gt;
gt;
gt; --
gt; Ltat42a
gt; ------------------------------------------------------------------------
gt; Ltat42a's Profile:
www.excelforum.com/member.php...oamp;userid=24735
gt; View this thread: www.excelforum.com/showthread...hreadid=497871
gt;

Bob Phillips Wrote:
gt; You should be able to adapt this
gt;
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Const WS_RANGE As String = quot;H1:H10quot;
gt;
gt; On Error GoTo ws_exit:
gt; Application.EnableEvents = False
gt; If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
gt; With Target
gt; Select Case .Value
gt; Case 1: .Interior.ColorIndex = 3 'red
gt; Case 2: .Interior.ColorIndex = 6 'yellow
gt; Case 3: .Interior.ColorIndex = 5 'blue
gt; Case 4: .Interior.ColorIndex = 10 'green
gt; End Select
gt; End With
gt; End If
gt;
gt; ws_exit:
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Ltat42aquot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; I made a spreadsheet for scheduling employees. I often grant leave
gt; gt; (vacation, sick, etc...etc...), and fill the open positions. I have
gt; gt; about 7 different variables. Conditional formatting works great, but
gt; gt; for only 3 of the 7 variables.
gt; gt;
gt; gt; I'm looking to fill the cell color and change the font color when I
gt; gt; enter
gt; gt; certain text into the cell, including empty cells.
gt; gt;
gt; gt; for instance...if a cell is empty - color =red
gt; gt; cell has quot;ALquot; - color=blue, text=white.
gt; gt; and so on....and so on - 7 different variables.
gt; gt;
gt; gt; Is this possible?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Ltat42a
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Ltat42a's Profile:
gt; www.excelforum.com/member.php...oamp;userid=24735
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=497871
gt; gt;

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 quot;SLquot;, 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
(B5:P9; B22:P25; B39:P42; amp; B56:P61).

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

Thanx...JF--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=497871
quot;Ltat42aquot; gt; wrote in
message ...
gt;
gt; Thanx Bob, that's a great start. In your example, when I input 3, I get
gt; a blue cell with black text, when I enter either 6, 5, or 10, I get
gt; nothing.

The example I gave only tests 1,2,3,4. You need to extend it.
gt;
gt; On my schedule, I have about 300 cells with text in it (i.e.
gt; 0800-1600).
gt; When someone requests leave, I will remove that text, and, I'm wanting
gt; that cell to shade red. This let's me know I have to fill that
gt; position.
gt; Now...in that same cell, I enter quot;SLquot;, the cell will change to green
gt; with white text. This will tell me that the person off is on sick
gt; leave, and I have filled their position with someone else.
gt;
gt; I can scroll down the schedule, look for red cells (no text in it),
gt; then fill those positions and enter the type of leave that was used.
gt;
gt; There will be 8 (not 7) different variables that I will be using. There
gt; is also four different ranges of cells that I need this to work in
gt; (B5:P9; B22:P25; B39:P42; amp; B56:P61).
gt;
gt; 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 = quot;B5:P9,B22:P25,B39:P42,B56:P61quot;

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 quot;quot;: .Interior.ColorIndex = xlCIRed
Case quot;xquot;: .Interior.ColorIndex = xlCIYellow
Case quot;yquot;: .Interior.ColorIndex = xlCIBlue
Case quot;SLquot;: .Interior.ColorIndex = xlCIGreen
.Font.ColorIndex = xlCIWhite
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
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 quot;quot;: .Interior.ColorIndex = xlCIRed
Case quot;ALquot;: .Interior.ColorIndex = xlCIBlue
Case quot;SLquot;: .Interior.ColorIndex = xlCIGreen
Case quot;STquot;: .Interior.ColorIndex = xlCIOrange
Case quot;ADquot;: .Interior.ColorIndex = xlCIViolet
Case quot;CLquot;: .Interior.ColorIndex = xlCIPink
Case quot;CTquot;: .Interior.ColorIndex = xlCIIndigo
Case quot;VOTquot;: .Interior.ColorIndex = xlCIBlack
Case quot;MOTquot;: .Interior.ColorIndex = xlCIBrown

The quot;ALquot;, quot;SLquot;, amp; quot;MOTquot; 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--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=497871I 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)

quot;Ltat42aquot; gt; wrote in
message ...
gt;
gt; Hey Bob...That's it! Just 1 more question.
gt; In the VB code you posted, I added several of the variables I needed.
gt; The shading works, but the font color does not, it returns a font color
gt; of black, I need it to return a white font. Here's what I have -
gt;
gt; Case quot;quot;: .Interior.ColorIndex = xlCIRed
gt; Case quot;ALquot;: .Interior.ColorIndex = xlCIBlue
gt; Case quot;SLquot;: .Interior.ColorIndex = xlCIGreen
gt; Case quot;STquot;: .Interior.ColorIndex = xlCIOrange
gt; Case quot;ADquot;: .Interior.ColorIndex = xlCIViolet
gt; Case quot;CLquot;: .Interior.ColorIndex = xlCIPink
gt; Case quot;CTquot;: .Interior.ColorIndex = xlCIIndigo
gt; Case quot;VOTquot;: .Interior.ColorIndex = xlCIBlack
gt; Case quot;MOTquot;: .Interior.ColorIndex = xlCIBrown
gt;
gt; The quot;ALquot;, quot;SLquot;, amp; quot;MOTquot; return a value of white, the others return a
gt; font color of black. I need them all white.
gt;
gt; If in the future, I need to add or change these variables, what
gt; considerations do I need?
gt;
gt; Thanx again...Jim
gt;
gt;
gt; --
gt; Ltat42a
gt; ------------------------------------------------------------------------
gt; Ltat42a's Profile:
www.excelforum.com/member.php...oamp;userid=24735
gt; View this thread: www.excelforum.com/showthread...hreadid=497871
gt;
You need to be explicit

Case quot;quot;: .Interior.ColorIndex = xlCIRed
Case quot;ALquot;: .Interior.ColorIndex = xlCIBlue
Case quot;SLquot;: .Interior.ColorIndex = xlCIGreen
Case quot;STquot;: .Interior.ColorIndex = xlCIOrange
Case quot;ADquot;: .Interior.ColorIndex = xlCIViolet
Case quot;CLquot;: .Interior.ColorIndex = xlCIPink
Case quot;CTquot;: .Interior.ColorIndex = xlCIIndigo
Case quot;VOTquot;: .Interior.ColorIndex = xlCIBlack
Case quot;MOTquot;: .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)

quot;Ltat42aquot; gt; wrote in
message ...
gt;
gt; Hey Bob...That's it! Just 1 more question.
gt; In the VB code you posted, I added several of the variables I needed.
gt; The shading works, but the font color does not, it returns a font color
gt; of black, I need it to return a white font. Here's what I have -
gt;
gt; Case quot;quot;: .Interior.ColorIndex = xlCIRed
gt; Case quot;ALquot;: .Interior.ColorIndex = xlCIBlue
gt; Case quot;SLquot;: .Interior.ColorIndex = xlCIGreen
gt; Case quot;STquot;: .Interior.ColorIndex = xlCIOrange
gt; Case quot;ADquot;: .Interior.ColorIndex = xlCIViolet
gt; Case quot;CLquot;: .Interior.ColorIndex = xlCIPink
gt; Case quot;CTquot;: .Interior.ColorIndex = xlCIIndigo
gt; Case quot;VOTquot;: .Interior.ColorIndex = xlCIBlack
gt; Case quot;MOTquot;: .Interior.ColorIndex = xlCIBrown
gt;
gt; The quot;ALquot;, quot;SLquot;, amp; quot;MOTquot; return a value of white, the others return a
gt; font color of black. I need them all white.
gt;
gt; If in the future, I need to add or change these variables, what
gt; considerations do I need?
gt;
gt; Thanx again...Jim
gt;
gt;
gt; --
gt; Ltat42a
gt; ------------------------------------------------------------------------
gt; Ltat42a's Profile:
www.excelforum.com/member.php...oamp;userid=24735
gt; View this thread: www.excelforum.com/showthread...hreadid=497871
gt;

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, the
cell shade is white, and the text is white. Is the VB code preventing a
cell default of white shade with black text? I can manually set these
and it works, just inquiring.

Thanx...Jim

See the attached sample -------------------------------------------------------------------
|Filename: schedsmpl2.zip |
|Download: www.excelforum.com/attachment.php?postid=4182 |
-------------------------------------------------------------------

--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=497871Not that I can see Jim.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ltat42aquot; gt; wrote in
message ...
gt;
gt; Hi Bob...that works great, just what I needed. Thank you.
gt;
gt; I see one thing, if I enter a new employee and enter their shift, the
gt; cell shade is white, and the text is white. Is the VB code preventing a
gt; cell default of white shade with black text? I can manually set these
gt; and it works, just inquiring.
gt;
gt; Thanx...Jim
gt;
gt; See the attached sample
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: schedsmpl2.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4182 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Ltat42a
gt; ------------------------------------------------------------------------
gt; Ltat42a's Profile:
www.excelforum.com/member.php...oamp;userid=24735
gt; View this thread: www.excelforum.com/showthread...hreadid=497871
gt;

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 quot;Aquot;, quot;Bquot;, quot;Cquot;, quot;Dquot;, or quot;Equot;, I need the cell shade to be
white, font color black.

Is it possible to add this in?

Thanx...Jim--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=497871