SHADING FOR MULTIPLE TEXT ENTRIES

S

shekpatrick

hello again,

more specifics on exactly what i am trying to do.

i have 300 rows (record entries) containing people's names. These folks
over the next 12 months are going to be going in a variety of locations.

I have a large amount of columns set up as dates.

I am going to limit the data entry into the date column/cell to the following:

DB, DN, DS, DO, DJ, HH, PCS, LV

As I am going to do a bunch of counting formulas for each day (so we can
figure out the number of folks in each location), so I need to make the text
entry into each cell.

What I do not know how to do (since conditional formating only allows 3
conditions)...is this:

In a cell, I enter "DB". This entry triggers the cell to fill with a
certain color shade, as well as change the entered text to the same color.

Here is the color scheme that I would like to use:

DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ
(red), HH (yellow), PCS (red), LV (black).

appreciate any help that anyone can provide!!! thanks in advance :))
 
J

JLatham

There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal??

Also, it would be a good thing to know the column IDs involved so that we
could be more precise in our testing of the cells -- but the code below
provides the basics.

Read carefully - this code must go into the worksheet code area for the
sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries.
To get to the proper place, open your workbook, select that sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
appears.

Copy the code below and paste it into the empty code module that appeared
when you clicked [View Code]. Close the VBA editor and give it a tryout.

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Else
'do nothing
End Select
End Sub

Hope this helps.
 
G

Gord Dibben

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim iColor As Long
Set R = Range("A1:A100") 'adjust to suit
If Intersect(Target, R) Is Nothing Or Target.Count > 1 Then Exit Sub
Vals = Array("DB", "DN", "DS", "DO", "DJ", "HH", "PCS", "LV")
Nums = Array(48, 4, 50, 8, 3, 6, 3, 1)
For i = LBound(Vals) To UBound(Vals)
If UCase(Target.Value) = Vals(i) Then iColor = Nums(i)
Next
With Target
.Interior.ColorIndex = iColor
End With
End Sub


Gord Dibben MS Excel MVP
 
S

shekpatrick

Mr Latham,

Your code was a huge help!! I did some tweaking, following your
example...and now i understand the question that you posed, "how/when do the
colors in these cells return to normal". I figured out how to return the interior color back to white when there is no entry in the cell.
Click to expand...

Once again, thank you very much!!!

Patrick

JLatham said:
There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal??

Also, it would be a good thing to know the column IDs involved so that we
could be more precise in our testing of the cells -- but the code below
provides the basics.

Read carefully - this code must go into the worksheet code area for the
sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries.
To get to the proper place, open your workbook, select that sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
appears.

Copy the code below and paste it into the empty code module that appeared
when you clicked [View Code]. Close the VBA editor and give it a tryout.

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Else
'do nothing
End Select
End Sub

Hope this helps.
shekpatrick said:
hello again,

more specifics on exactly what i am trying to do.

i have 300 rows (record entries) containing people's names. These folks
over the next 12 months are going to be going in a variety of locations.

I have a large amount of columns set up as dates.

I am going to limit the data entry into the date column/cell to the following:

DB, DN, DS, DO, DJ, HH, PCS, LV

As I am going to do a bunch of counting formulas for each day (so we can
figure out the number of folks in each location), so I need to make the text
entry into each cell.

What I do not know how to do (since conditional formating only allows 3
conditions)...is this:

In a cell, I enter "DB". This entry triggers the cell to fill with a
certain color shade, as well as change the entered text to the same color.

Here is the color scheme that I would like to use:

DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ
(red), HH (yellow), PCS (red), LV (black).

appreciate any help that anyone can provide!!! thanks in advance :))
Click to expand...
Click to expand...
 
S

shekpatrick

Mr Latham,

My previous response was premature...here is the code that I have right now:

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6
Const colorLavender = 39
Const colorLightOrange = 45
Const colorWhite = 2
Const colorViolet = 13

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorViolet
Target.Font.ColorIndex = colorViolet
Case Is = "PG"
Target.Interior.ColorIndex = colorLavender
Target.Font.ColorIndex = colorLavender
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Is = "TD"
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 45
Case Is = ""
Target.Interior.ColorIndex = 2
Case Else
'do nothing
End Select
End Sub

The column IDs are AF thru IU.

My attempt to return the cell color to white when the text is deleted works,
but you have to click/keystroke each cell individually...I can't select a
cell range within a row and hit delete, and have the color return to white
(though the text does delete).

I also found out that when pasting in columns AF thru IU....while using a
filter selection that limits rows, causes the paste to go into every cell. I
can work around this by limiting my pasting to each row (record).

thanks in advance!

patrick


JLatham said:
There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal??

Also, it would be a good thing to know the column IDs involved so that we
could be more precise in our testing of the cells -- but the code below
provides the basics.

Read carefully - this code must go into the worksheet code area for the
sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries.
To get to the proper place, open your workbook, select that sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
appears.

Copy the code below and paste it into the empty code module that appeared
when you clicked [View Code]. Close the VBA editor and give it a tryout.

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Else
'do nothing
End Select
End Sub

Hope this helps.
shekpatrick said:
hello again,

more specifics on exactly what i am trying to do.

i have 300 rows (record entries) containing people's names. These folks
over the next 12 months are going to be going in a variety of locations.

I have a large amount of columns set up as dates.

I am going to limit the data entry into the date column/cell to the following:

DB, DN, DS, DO, DJ, HH, PCS, LV

As I am going to do a bunch of counting formulas for each day (so we can
figure out the number of folks in each location), so I need to make the text
entry into each cell.

What I do not know how to do (since conditional formating only allows 3
conditions)...is this:

In a cell, I enter "DB". This entry triggers the cell to fill with a
certain color shade, as well as change the entered text to the same color.

Here is the color scheme that I would like to use:

DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ
(red), HH (yellow), PCS (red), LV (black).

appreciate any help that anyone can provide!!! thanks in advance :))
Click to expand...
Click to expand...
 
J

JLatham

Did you get this straightened out, perhaps with Gord Dibben's help? Or not?
The system didn't notify me that you'd posted this and I just happened upon
this today - don't want to leave you hanging if you need more assistance.

shekpatrick said:
Mr Latham,

My previous response was premature...here is the code that I have right now:

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6
Const colorLavender = 39
Const colorLightOrange = 45
Const colorWhite = 2
Const colorViolet = 13

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorViolet
Target.Font.ColorIndex = colorViolet
Case Is = "PG"
Target.Interior.ColorIndex = colorLavender
Target.Font.ColorIndex = colorLavender
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Is = "TD"
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 45
Case Is = ""
Target.Interior.ColorIndex = 2
Case Else
'do nothing
End Select
End Sub

The column IDs are AF thru IU.

My attempt to return the cell color to white when the text is deleted works,
but you have to click/keystroke each cell individually...I can't select a
cell range within a row and hit delete, and have the color return to white
(though the text does delete).

I also found out that when pasting in columns AF thru IU....while using a
filter selection that limits rows, causes the paste to go into every cell. I
can work around this by limiting my pasting to each row (record).

thanks in advance!

patrick


JLatham said:
There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal??

Also, it would be a good thing to know the column IDs involved so that we
could be more precise in our testing of the cells -- but the code below
provides the basics.

Read carefully - this code must go into the worksheet code area for the
sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries.
To get to the proper place, open your workbook, select that sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
appears.

Copy the code below and paste it into the empty code module that appeared
when you clicked [View Code]. Close the VBA editor and give it a tryout.

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Else
'do nothing
End Select
End Sub

Hope this helps.
shekpatrick said:
hello again,

more specifics on exactly what i am trying to do.

i have 300 rows (record entries) containing people's names. These folks
over the next 12 months are going to be going in a variety of locations.

I have a large amount of columns set up as dates.

I am going to limit the data entry into the date column/cell to the following:

DB, DN, DS, DO, DJ, HH, PCS, LV

As I am going to do a bunch of counting formulas for each day (so we can
figure out the number of folks in each location), so I need to make the text
entry into each cell.

What I do not know how to do (since conditional formating only allows 3
conditions)...is this:

In a cell, I enter "DB". This entry triggers the cell to fill with a
certain color shade, as well as change the entered text to the same color.

Here is the color scheme that I would like to use:

DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ
(red), HH (yellow), PCS (red), LV (black).

appreciate any help that anyone can provide!!! thanks in advance :))
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

I posted just one one reply Jerry.

No help for OP from me.

OP seemed to want to follow your thread with the Select Case statements.


Gord

Did you get this straightened out, perhaps with Gord Dibben's help? Or not?
The system didn't notify me that you'd posted this and I just happened upon
this today - don't want to leave you hanging if you need more assistance.

shekpatrick said:
Mr Latham,

My previous response was premature...here is the code that I have right now:

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6
Const colorLavender = 39
Const colorLightOrange = 45
Const colorWhite = 2
Const colorViolet = 13

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorViolet
Target.Font.ColorIndex = colorViolet
Case Is = "PG"
Target.Interior.ColorIndex = colorLavender
Target.Font.ColorIndex = colorLavender
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Is = "TD"
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 45
Case Is = ""
Target.Interior.ColorIndex = 2
Case Else
'do nothing
End Select
End Sub

The column IDs are AF thru IU.

My attempt to return the cell color to white when the text is deleted works,
but you have to click/keystroke each cell individually...I can't select a
cell range within a row and hit delete, and have the color return to white
(though the text does delete).

I also found out that when pasting in columns AF thru IU....while using a
filter selection that limits rows, causes the paste to go into every cell. I
can work around this by limiting my pasting to each row (record).

thanks in advance!

patrick


JLatham said:
There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal??

Also, it would be a good thing to know the column IDs involved so that we
could be more precise in our testing of the cells -- but the code below
provides the basics.

Read carefully - this code must go into the worksheet code area for the
sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries.
To get to the proper place, open your workbook, select that sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
appears.

Copy the code below and paste it into the empty code module that appeared
when you clicked [View Code]. Close the VBA editor and give it a tryout.

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Else
'do nothing
End Select
End Sub

Hope this helps.
:

hello again,

more specifics on exactly what i am trying to do.

i have 300 rows (record entries) containing people's names. These folks
over the next 12 months are going to be going in a variety of locations.

I have a large amount of columns set up as dates.

I am going to limit the data entry into the date column/cell to the following:

DB, DN, DS, DO, DJ, HH, PCS, LV

As I am going to do a bunch of counting formulas for each day (so we can
figure out the number of folks in each location), so I need to make the text
entry into each cell.

What I do not know how to do (since conditional formating only allows 3
conditions)...is this:

In a cell, I enter "DB". This entry triggers the cell to fill with a
certain color shade, as well as change the entered text to the same color.

Here is the color scheme that I would like to use:

DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ
(red), HH (yellow), PCS (red), LV (black).

appreciate any help that anyone can provide!!! thanks in advance :))
Click to expand...
Click to expand...
Click to expand...
 
J

JLatham

Thanks,
I'll wait to see if the OP asks for more assistance - seems to still be in
need of some, but I need to know if they're still following the thread and
still need help.

Gord Dibben said:
I posted just one one reply Jerry.

No help for OP from me.

OP seemed to want to follow your thread with the Select Case statements.


Gord

Did you get this straightened out, perhaps with Gord Dibben's help? Or not?
The system didn't notify me that you'd posted this and I just happened upon
this today - don't want to leave you hanging if you need more assistance.

shekpatrick said:
Mr Latham,

My previous response was premature...here is the code that I have right now:

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6
Const colorLavender = 39
Const colorLightOrange = 45
Const colorWhite = 2
Const colorViolet = 13

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorViolet
Target.Font.ColorIndex = colorViolet
Case Is = "PG"
Target.Interior.ColorIndex = colorLavender
Target.Font.ColorIndex = colorLavender
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Is = "TD"
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 45
Case Is = ""
Target.Interior.ColorIndex = 2
Case Else
'do nothing
End Select
End Sub

The column IDs are AF thru IU.

My attempt to return the cell color to white when the text is deleted works,
but you have to click/keystroke each cell individually...I can't select a
cell range within a row and hit delete, and have the color return to white
(though the text does delete).

I also found out that when pasting in columns AF thru IU....while using a
filter selection that limits rows, causes the paste to go into every cell. I
can work around this by limiting my pasting to each row (record).

thanks in advance!

patrick


:

There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal??

Also, it would be a good thing to know the column IDs involved so that we
could be more precise in our testing of the cells -- but the code below
provides the basics.

Read carefully - this code must go into the worksheet code area for the
sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries.
To get to the proper place, open your workbook, select that sheet and
right-click on the sheet's name tab and choose [View Code] from the list that
appears.

Copy the code below and paste it into the empty code module that appeared
when you clicked [View Code]. Close the VBA editor and give it a tryout.

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Else
'do nothing
End Select
End Sub

Hope this helps.
:

hello again,

more specifics on exactly what i am trying to do.

i have 300 rows (record entries) containing people's names. These folks
over the next 12 months are going to be going in a variety of locations.

I have a large amount of columns set up as dates.

I am going to limit the data entry into the date column/cell to the following:

DB, DN, DS, DO, DJ, HH, PCS, LV

As I am going to do a bunch of counting formulas for each day (so we can
figure out the number of folks in each location), so I need to make the text
entry into each cell.

What I do not know how to do (since conditional formating only allows 3
conditions)...is this:

In a cell, I enter "DB". This entry triggers the cell to fill with a
certain color shade, as well as change the entered text to the same color.

Here is the color scheme that I would like to use:

DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ
(red), HH (yellow), PCS (red), LV (black).

appreciate any help that anyone can provide!!! thanks in advance :))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data entered in one record is shifting to others 1
color shading for text entries 3
slanted cell shading 8
Excel Conditioning Formatting 3
MS Excel 2002 : Conditional formating by shading cells 2
How to extract separated text by many commas in Excel? 0
Shading cells 1
Remove Hyperlinks Without Changing background shading 14

Top