A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Assuming I did what you suggested, I did try formatting it as text but still
have the same problem. I guess I will just have to put everything in it's
own cell! It just seems to me that there ought to be a way to say to Excel,
"Hey, when you go to a cell and see three numbers, make them all the color
that I want you to make them." I should have thought ahead! Thanks for
chipping in.

"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
....it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

I will sure give it a try and let you know how it goes. Can't thank you
enough for your assistance!

"Ron Coderre" wrote:

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Ron:

One more question if I might. Does the conditional formatting feature in
Excel really limit me (as it appears to) to only three conditions? What if I
have 20 numbers in a spreadsheet, all in their own cells, and I want to be
able to see by looking at the sheet where all the numbers are by assigning
them all their own color? I would think there would be any easy way to do
this in Excel, but ... .

"Ron Coderre" wrote:

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Patrick! 20 separate conditional formats! That's the kind of thing you
mention right up front, not as an "oh, by the way..." deep into the thread.
<g
Do you realize that after you used up the major colors you'd start delving
into shades of blue, green, etc? Might not be very practical.

How about this.....
Example:
with
B2:I200 containing values (or blanks)

[Ctrl]+F.......that's the shortcut for <edit<find
Find what: 5
Check: Match entire cell contents (you only have to do this once)
Click the [Find all] button
........That lists all matching cells, but selects only one.
While the Find window is still open
[Ctrl]+A.......that will select ALL of the cells that contain only 5

OR....maybe this
Select the entire area to be impacted
Set a Conditional Format for all cells that match the value of $A$1 (yellow
background, maybe).

Now....whatever you enter into A1 will cause the CF to engage on all
matching cells.

Is either of those something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

One more question if I might. Does the conditional formatting feature in
Excel really limit me (as it appears to) to only three conditions? What if I
have 20 numbers in a spreadsheet, all in their own cells, and I want to be
able to see by looking at the sheet where all the numbers are by assigning
them all their own color? I would think there would be any easy way to do
this in Excel, but ... .

"Ron Coderre" wrote:

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Ron:

My apologies for not being clear about what I was trying to accomplish. I
am going to try this afternoon to apply some of the suggestions you made. It
would just have been so much easier if there wasn't this limit on conditional
formatting. When I select the cells to be affected I am only allowed to
"add" two more conditions and I need to be able to add 15 after the original.
I was surprised that Excel wouldn't let me do that and guess I should have
checked beforehand. Let me se if I can explain what I am trying to do.

I have about 25 students for whom I have to schedule classes through January
'08. While they all take the same classes, they start at different times.
So, for example, the first group might be taking BN1108 in January while the
next class takes it in April. There are 11 of these separate groups at the
moment. For each group at any given time there are two courses ongoing.
Each course has its own separate cell somewhere in the row for each group.
In other words, Column C, Row 1 may have BN1108 for group 1 and Column F, row
9 may have the same class for group 2. All total, each group has 16 separate
courses they all must take tp get their degree.

My goal is to have a spreadsheet that when opened will have all these
separate courses highlighted/colored/formatted in some way so that I can see
at a glance which group is taking what course at what time. This is helpful
because if I have a student who has to drop, I can easily see when the course
he dropped will roll around again. It will also show if somehow a course was
"double booked" in error.

Once again, I thank you so much for all of your help and aplogize for not
being clear. I will try your suggestions if you think they will fit the bill
and will offer my "novice" Excel standing as an explanation if not as an
excuse!

"Ron Coderre" wrote:

Patrick! 20 separate conditional formats! That's the kind of thing you
mention right up front, not as an "oh, by the way..." deep into the thread.
<g
Do you realize that after you used up the major colors you'd start delving
into shades of blue, green, etc? Might not be very practical.

How about this.....
Example:
with
B2:I200 containing values (or blanks)

[Ctrl]+F.......that's the shortcut for <edit<find
Find what: 5
Check: Match entire cell contents (you only have to do this once)
Click the [Find all] button
.......That lists all matching cells, but selects only one.
While the Find window is still open
[Ctrl]+A.......that will select ALL of the cells that contain only 5

OR....maybe this
Select the entire area to be impacted
Set a Conditional Format for all cells that match the value of $A$1 (yellow
background, maybe).

Now....whatever you enter into A1 will cause the CF to engage on all
matching cells.

Is either of those something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

One more question if I might. Does the conditional formatting feature in
Excel really limit me (as it appears to) to only three conditions? What if I
have 20 numbers in a spreadsheet, all in their own cells, and I want to be
able to see by looking at the sheet where all the numbers are by assigning
them all their own color? I would think there would be any easy way to do
this in Excel, but ... .

"Ron Coderre" wrote:

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

How about this approach, instead...

If you construct your data like the below table, you could use an AutoFilter
to quickly get group/course information.

Example:
With this table in A1:C11
GroupRef ClassRef SchedRef
Group_01 Class_01 Jan-07
Group_02 Class_01 Feb-07
Group_03 Class_01 Mar-07
Group_04 Class_01 Apr-07
Group_05 Class_01 May-07
Group_01 Class_02 Jan-07
Group_02 Class_02 Feb-07
Group_03 Class_02 Mar-07
Group_04 Class_02 Apr-07
Group_05 Class_02 May-07

Select the data range
Then, from the Excel main menu:
<data<filter<autofilter
That will put dropdown arrows in the column headings.
Click on a dropdown arrow and select an item restrict the list to that item.

For instance, if you select Class_01 from the ClassRef list,
you'll see all of the dates it is offered and for which groups.
You could then select Group_02 from the GroupRef list to further restrict
the list to only Class_01 offered to Group_02

To redisplay all records: <data<filter<show all

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

My apologies for not being clear about what I was trying to accomplish. I
am going to try this afternoon to apply some of the suggestions you made. It
would just have been so much easier if there wasn't this limit on conditional
formatting. When I select the cells to be affected I am only allowed to
"add" two more conditions and I need to be able to add 15 after the original.
I was surprised that Excel wouldn't let me do that and guess I should have
checked beforehand. Let me se if I can explain what I am trying to do.

I have about 25 students for whom I have to schedule classes through January
'08. While they all take the same classes, they start at different times.
So, for example, the first group might be taking BN1108 in January while the
next class takes it in April. There are 11 of these separate groups at the
moment. For each group at any given time there are two courses ongoing.
Each course has its own separate cell somewhere in the row for each group.
In other words, Column C, Row 1 may have BN1108 for group 1 and Column F, row
9 may have the same class for group 2. All total, each group has 16 separate
courses they all must take tp get their degree.

My goal is to have a spreadsheet that when opened will have all these
separate courses highlighted/colored/formatted in some way so that I can see
at a glance which group is taking what course at what time. This is helpful
because if I have a student who has to drop, I can easily see when the course
he dropped will roll around again. It will also show if somehow a course was
"double booked" in error.

Once again, I thank you so much for all of your help and aplogize for not
being clear. I will try your suggestions if you think they will fit the bill
and will offer my "novice" Excel standing as an explanation if not as an
excuse!

"Ron Coderre" wrote:

Patrick! 20 separate conditional formats! That's the kind of thing you
mention right up front, not as an "oh, by the way..." deep into the thread.
<g
Do you realize that after you used up the major colors you'd start delving
into shades of blue, green, etc? Might not be very practical.

How about this.....
Example:
with
B2:I200 containing values (or blanks)

[Ctrl]+F.......that's the shortcut for <edit<find
Find what: 5
Check: Match entire cell contents (you only have to do this once)
Click the [Find all] button
.......That lists all matching cells, but selects only one.
While the Find window is still open
[Ctrl]+A.......that will select ALL of the cells that contain only 5

OR....maybe this
Select the entire area to be impacted
Set a Conditional Format for all cells that match the value of $A$1 (yellow
background, maybe).

Now....whatever you enter into A1 will cause the CF to engage on all
matching cells.

Is either of those something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

One more question if I might. Does the conditional formatting feature in
Excel really limit me (as it appears to) to only three conditions? What if I
have 20 numbers in a spreadsheet, all in their own cells, and I want to be
able to see by looking at the sheet where all the numbers are by assigning
them all their own color? I would think there would be any easy way to do
this in Excel, but ... .

"Ron Coderre" wrote:

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance

Ron:

I will give your suggestion a try this afternoon and will certainly update
you! Thanks again!

"Ron Coderre" wrote:

How about this approach, instead...

If you construct your data like the below table, you could use an AutoFilter
to quickly get group/course information.

Example:
With this table in A1:C11
GroupRef ClassRef SchedRef
Group_01 Class_01 Jan-07
Group_02 Class_01 Feb-07
Group_03 Class_01 Mar-07
Group_04 Class_01 Apr-07
Group_05 Class_01 May-07
Group_01 Class_02 Jan-07
Group_02 Class_02 Feb-07
Group_03 Class_02 Mar-07
Group_04 Class_02 Apr-07
Group_05 Class_02 May-07

Select the data range
Then, from the Excel main menu:
<data<filter<autofilter
That will put dropdown arrows in the column headings.
Click on a dropdown arrow and select an item restrict the list to that item.

For instance, if you select Class_01 from the ClassRef list,
you'll see all of the dates it is offered and for which groups.
You could then select Group_02 from the GroupRef list to further restrict
the list to only Class_01 offered to Group_02

To redisplay all records: <data<filter<show all

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

My apologies for not being clear about what I was trying to accomplish. I
am going to try this afternoon to apply some of the suggestions you made. It
would just have been so much easier if there wasn't this limit on conditional
formatting. When I select the cells to be affected I am only allowed to
"add" two more conditions and I need to be able to add 15 after the original.
I was surprised that Excel wouldn't let me do that and guess I should have
checked beforehand. Let me se if I can explain what I am trying to do.

I have about 25 students for whom I have to schedule classes through January
'08. While they all take the same classes, they start at different times.
So, for example, the first group might be taking BN1108 in January while the
next class takes it in April. There are 11 of these separate groups at the
moment. For each group at any given time there are two courses ongoing.
Each course has its own separate cell somewhere in the row for each group.
In other words, Column C, Row 1 may have BN1108 for group 1 and Column F, row
9 may have the same class for group 2. All total, each group has 16 separate
courses they all must take tp get their degree.

My goal is to have a spreadsheet that when opened will have all these
separate courses highlighted/colored/formatted in some way so that I can see
at a glance which group is taking what course at what time. This is helpful
because if I have a student who has to drop, I can easily see when the course
he dropped will roll around again. It will also show if somehow a course was
"double booked" in error.

Once again, I thank you so much for all of your help and aplogize for not
being clear. I will try your suggestions if you think they will fit the bill
and will offer my "novice" Excel standing as an explanation if not as an
excuse!

"Ron Coderre" wrote:

Patrick! 20 separate conditional formats! That's the kind of thing you
mention right up front, not as an "oh, by the way..." deep into the thread.
<g
Do you realize that after you used up the major colors you'd start delving
into shades of blue, green, etc? Might not be very practical.

How about this.....
Example:
with
B2:I200 containing values (or blanks)

[Ctrl]+F.......that's the shortcut for <edit<find
Find what: 5
Check: Match entire cell contents (you only have to do this once)
Click the [Find all] button
.......That lists all matching cells, but selects only one.
While the Find window is still open
[Ctrl]+A.......that will select ALL of the cells that contain only 5

OR....maybe this
Select the entire area to be impacted
Set a Conditional Format for all cells that match the value of $A$1 (yellow
background, maybe).

Now....whatever you enter into A1 will cause the CF to engage on all
matching cells.

Is either of those something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

One more question if I might. Does the conditional formatting feature in
Excel really limit me (as it appears to) to only three conditions? What if I
have 20 numbers in a spreadsheet, all in their own cells, and I want to be
able to see by looking at the sheet where all the numbers are by assigning
them all their own color? I would think there would be any easy way to do
this in Excel, but ... .

"Ron Coderre" wrote:

Please don't shy away from a tiny little pre-built formula just yet....

Try this:
Hold down the [alt] key and press the [f11] key....that opens the vba editor
Right-click on the workbook name
Select: Insert module.....that will open a General Module

Just copy this code and paste it into the module:

'--------start of code--------
Function CellFormula(rngCell As range) As String
Dim Bullpen As String

If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function
'--------end of code--------

That's it! Done!

Now you can use the cellformula() function.
Here's a way to test it:
Put any value in cell A1
B1: =cellformula(A1)

experiment with A1 values
(easy...yes?)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

Ron:

Thanks again. The good news is that this sounds GREAT! The "bad" news is
it sounds way beyond what I know how to do. :) Maybe I could put what you
want into a "general module" but at my level don't even know what that is or
where I would find it. I'd love to try and do appreciate all of your input.
In the meantime, I am transferring things to individual cells as a temporary
fix.

"Ron Coderre" wrote:

Well.....my response was pretty much off the mark, but see if this one gets
you what you want....

No regular Excel formula can look at the contents of a cell the way you want
it to.

For example:
If A1: =10/10
Then this formula =AND(A1=1,TEXT(A1,"general")="1") returns TRUE
...it can't "see" the actual contents.
and
this formula =CELL("contents",A1) returns 1 for the same reason.

However....Maybe you could use a User Defined Function?:
Put this code into a Genral Module...

Function CellFormula(rngCell As range) As String
Dim Bullpen As String
If rngCell.Cells.Count = 1 Then
Bullpen = CStr(rngCell.Formula)
If Len(Bullpen) 0 Then
CellFormula = Bullpen
Else
CellFormula = "(cell is empty)"
End If
Else
CellFormula = "invalid range"
End If
End Function

Then, for cell A1, the CF formula could be:
=cellformula(A1)="1"
and I think THAT would work.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

I believe formatting individual characters only works for text....not numbers.

Maybe try this?:
Format the cells as Text.
Then right-align them and change the individual character colors.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Patrick" wrote:

A new challenge for me!

I am entering different numbers in the same cell. I need the numbers to be
a different color. Is it possible to have Excel do this? I have tried
conditional formatting but I have a problem. If, for example, I want the
number 1 to be red it will be red if it is the ONLY number in the cell. As
soon as another number is entered, both are are the default color.

I am hoping I can do this without putting everything in it's own separate
cell.

Thanks in advance