ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   SHADING FOR MULTIPLE TEXT ENTRIES (https://www.excelbanter.com/new-users-excel/214153-shading-multiple-text-entries.html)

shekpatrick
SHADING FOR MULTIPLE TEXT ENTRIES
 
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 :))

JLatham
SHADING FOR MULTIPLE TEXT ENTRIES
 
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" wrote:

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 :))

Gord Dibben
SHADING FOR MULTIPLE TEXT ENTRIES
 
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

On Thu, 18 Dec 2008 09:46:03 -0800, shekpatrick
wrote:

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 :))

shekpatrick
SHADING FOR MULTIPLE TEXT ENTRIES
 
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.

Once again, thank you very much!!!

Patrick

"JLatham" wrote:

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" wrote:

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 :))

shekpatrick
SHADING FOR MULTIPLE TEXT ENTRIES
 
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" wrote:

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" wrote:

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 :))

JLatham
SHADING FOR MULTIPLE TEXT ENTRIES
 
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" wrote:

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" wrote:

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" wrote:

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 :))

Gord Dibben
SHADING FOR MULTIPLE TEXT ENTRIES
 
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

On Sun, 4 Jan 2009 18:19:01 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

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" wrote:

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" wrote:

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" wrote:

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 :))

JLatham
SHADING FOR MULTIPLE TEXT ENTRIES
 
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" wrote:

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

On Sun, 4 Jan 2009 18:19:01 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

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" wrote:

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" wrote:

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" wrote:

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 :))


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com