More than 3 conditional formats?

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=497871
>

Originally Posted by 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=497871
>

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

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

Thanx...JF

"Ltat42a" <[email protected]> wrote 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.

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
> (B5:P9; B22:P25; B39:P42; & B56:P61).
>
> 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 = "B5:P9,B22:P25,B39:P42,B56:P61"

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

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)

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=497871
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=497871
>

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

Not that I can see Jim.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> 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: http://www.excelforum.com/attachment.php?postid=4182 |
> +-------------------------------------------------------------------+
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=497871
>

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

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 = "B5:P9,B22:P25,B39:P42,B56:P61"

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Ltat42a
> ------------------------------------------------------------------------
> Ltat42a's Profile:
http://www.excelforum.com/member.php...o&userid=24735
> View this thread: http://www.excelforum.com/showthread...hreadid=497871
>

Originally Posted by Bob Phillips

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

"Ltat42a" <[email protected]> wrote in
message news:[email protected]...
>
> Bob...Thanx for all your help...I really appreciate it.

It has been my pleasure Jim.