I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

Conditional Formatting is limited to the 3 options. But you can simulate
more using VBA coding. The sample below is from an actual project I did
where we needed more options based on the text content of the cells. Change
the tests for conditions to meet your needs, or if you have problems with
that, post and I'll try to help. This has to go into each sheet's _Change
event as shown - each sheet meaning each sheet where you want this
conditional formatting to take place.

The Application.Intersect() function tests to see if the currently selected
cell (Target) intersects with an area on the worksheet that you define.
That's defined in the code as variable CellsToExamine, change that range for
your setup. If you wanted all of column A, you could use A1:A65536
(possibly simply A:A but I've never used it that way for an .Intersect
operation).

The code shows how to change both the background color of the cell and how
to change the font color. If you need to change to other colors or do things
like make the font bold, simply record macros doing what you need to do and
follow that model to making changes. Making the font Bold is easy also:
Target.Font.Bold = True
will make it bold,
Target.Font.Bold = False
will make it normal.

This page will tell you how to get into the VB Editor and add code to a
worksheet, which you need to do for this:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Private Sub Worksheet_Change(ByVal Target As Range)
'any time a change to a cell is made on this worksheet
'this event takes place
'This code checks to see if the cell where a change
'took place is within a certain defined group of cells
'if it did take place within the group, the current
'contents of the changed cell is examined and the
'fill color of the cell is set based on it
'
'the end effect it to give you an extended
'Conditional Format function
'
Dim iSect As Range
Dim CharRangeName As String
Dim CellsToExamine As String

CellsToExamine = "A1:A100" ' change as needed
'for just 1 entire column
Set iSect = Application.Intersect(Range(Target.Address), _
Range("CellsToExamine"))
If (iSect Is Nothing) Then
Exit Sub
End If
'Excel cells are not case sensitive, but
'VB code is, so we make sure that we make
'valid comparisons
'TRIM removes leading/trailing white space
'UCASE converts lowercase characters to uppercase
Select Case Trim(UCase(iSect.Text))
Case Is = "FI"
Target.Interior.ColorIndex = 4 ' Bright Green
Target.Font.ColorIndex = 9 ' Dark Red
Case Is = "LI"
Target.Interior.ColorIndex = 6 ' Bright Yellow
Case Is = "PI"
Target.Interior.ColorIndex = 45 ' Orange
Case Is = "NI"
Target.Interior.ColorIndex = 3 ' Bright Red
Case Else
'if anything other than those 4, no color
Target.Interior.ColorIndex = xlNone ' No Fill
Target.Font.ColorIndex = xlAutomatic ' standard
End Select
Set iSect = Nothing ' release resources
End Sub

"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

JLatham, Thanks so much for the help. I see on the Excel beta 2007 that
more than 3 formatings is possible. I will work with what you've given me
and and let you know how it works out. Thanks again. brillopad52

Feel free to contact me at jlatham @ jlathamsite. com (no spaces) if you need
more help that may seem inappropriate or too clumsy to provide here in the
forum. Replying here will also get my attention.

Just keep in mind that Office 12 is still in Beta, so if this is a mission
critical or similar setup, you may want to use it with caution.

"brillopad52" wrote:



"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

JLatham, Thanks so much for the help. I see on the Excel beta 2007 that
more than 3 formatings is possible. I will work with what you've given me
and and let you know how it works out. Thanks again. brillopad52

I have used this code to work on a problem that I am having getting more the
3 colors out of Excel 2000. I am a code novice so please bear with me. I have
modified this code and it works great if I am entering data directly to the
worksheet. However, in my case the data is being brought to this sheet from
another based on a formula and the colors do not change when I update the
other sheet. How do I need to modify this code to make that work? Or do I
need to use a different event? If yes, how would the coding for that event
look like?

thanks

--
MikeA


"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

Mike,
There's really not another place to get the code to act like that without
some kind of code change - as you've figured out.
Excel does not trigger the change event when the evaluation of a formula
results in a change.
There are a couple of ways to tackle this. One is to continue to use the
_Change() event and have it watch for changes to cells that are used in the
formulas in the cells that you do want to change color for. When a change
happens in one of those, you look at the ones that may need a color change
and see if it needs to be done.
The second way (possibly much busier) is to use the _Calculate() event and
check the values each time that event triggers.

I think you will find this discussion right on target with what you are
trying to do. Read through it completely because it ends up being a
situation like you are describing: change in cell in one column causes other
changes in other cells, and it is the other cells that need the format change:
http://www.microsoft.com/office/comm...2-576b132090f2

Good luck.


"MikeA" wrote:

I have used this code to work on a problem that I am having getting more the
3 colors out of Excel 2000. I am a code novice so please bear with me. I have
modified this code and it works great if I am entering data directly to the
worksheet. However, in my case the data is being brought to this sheet from
another based on a formula and the colors do not change when I update the
other sheet. How do I need to modify this code to make that work? Or do I
need to use a different event? If yes, how would the coding for that event
look like?

thanks

--
MikeA


"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

Hi,
I have tried changing the code you referred me to to resolve my problem, but
have had no success. If I give you some details I was wondering if you could
suggest the proper code.

The data that is being entered that would trigger the change event is in
cells on sheet1 of the workbook. The cells that need to change colors are on
sheet2 of the same workbook.

On sheet 1 the data is in the range G5:I238. Each row is a project and
columns G, H, & I represent 3 task that have to be done for each project.
There are 5 codes that could go into any of these cells (IP, R, C, N/A, &
O/H) that represent the status.

Sheet2 is a summary and the values from sheet1 are pulled to cells on this
sheet using a simple formula that pulls the value forward and if the cell is
blank replaces the 0 with a blank (""). These cells on sheet2 need to change
colors based on the values from the cells on sheet1. These cells are in the
range C3:P92.

Data moves like this:

data from sheet1 cells G133, H133, I133 appear in sheets cells C51, C52, &
C53.

Since I started working on this they have added a new requirement. Sheet2
has a target Completion Date for each project in the range C3:P3. If todays
date is greater then that date they would like the cell color to be RED
rather then its usual color unless the code is "C" (completed).

I would greatly appreciate any help you could give me.

thanks

MikeA



--
MikeA


"JLatham" wrote:

Mike,
There's really not another place to get the code to act like that without
some kind of code change - as you've figured out.
Excel does not trigger the change event when the evaluation of a formula
results in a change.
There are a couple of ways to tackle this. One is to continue to use the
_Change() event and have it watch for changes to cells that are used in the
formulas in the cells that you do want to change color for. When a change
happens in one of those, you look at the ones that may need a color change
and see if it needs to be done.
The second way (possibly much busier) is to use the _Calculate() event and
check the values each time that event triggers.

I think you will find this discussion right on target with what you are
trying to do. Read through it completely because it ends up being a
situation like you are describing: change in cell in one column causes other
changes in other cells, and it is the other cells that need the format change:
http://www.microsoft.com/office/comm...2-576b132090f2

Good luck.


"MikeA" wrote:

I have used this code to work on a problem that I am having getting more the
3 colors out of Excel 2000. I am a code novice so please bear with me. I have
modified this code and it works great if I am entering data directly to the
worksheet. However, in my case the data is being brought to this sheet from
another based on a formula and the colors do not change when I update the
other sheet. How do I need to modify this code to make that work? Or do I
need to use a different event? If yes, how would the coding for that event
look like?

thanks

--
MikeA


"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

Ok, looks to me like we've added another level of complexity into things: a
second sheet!

Can you rewrite your explanation above in the following manner:

Provide the sheet name and address that will be changed by the user that
cause other cells to change, and then tell me which cells on which sheet(s)
need to change color, Kind of like this:
On Sheet1 user enters information into G, H or I (rows 5:238)
When value in G5:G238 changes, then on Sheet2 cells C5:C238 needs to change
(on same/corresponding row)

kind of like that - but I'm having a problem figuring out how changes in
columns G, H, I from rows 5 through 238 on Sheet1 correspond to columns C
through P and rows 3 through 92.

Perhaps even an example workbook sent to me as an attachment to an email?
With LOTS of explanation! If so, then send to (remove spaces)
HelpFrom @ jlathamsite.com



"MikeA" wrote:

Hi,
I have tried changing the code you referred me to to resolve my problem, but
have had no success. If I give you some details I was wondering if you could
suggest the proper code.

The data that is being entered that would trigger the change event is in
cells on sheet1 of the workbook. The cells that need to change colors are on
sheet2 of the same workbook.

On sheet 1 the data is in the range G5:I238. Each row is a project and
columns G, H, & I represent 3 task that have to be done for each project.
There are 5 codes that could go into any of these cells (IP, R, C, N/A, &
O/H) that represent the status.

Sheet2 is a summary and the values from sheet1 are pulled to cells on this
sheet using a simple formula that pulls the value forward and if the cell is
blank replaces the 0 with a blank (""). These cells on sheet2 need to change
colors based on the values from the cells on sheet1. These cells are in the
range C3:P92.

Data moves like this:

data from sheet1 cells G133, H133, I133 appear in sheets cells C51, C52, &
C53.

Since I started working on this they have added a new requirement. Sheet2
has a target Completion Date for each project in the range C3:P3. If todays
date is greater then that date they would like the cell color to be RED
rather then its usual color unless the code is "C" (completed).

I would greatly appreciate any help you could give me.

thanks

MikeA



--
MikeA


"JLatham" wrote:

Mike,
There's really not another place to get the code to act like that without
some kind of code change - as you've figured out.
Excel does not trigger the change event when the evaluation of a formula
results in a change.
There are a couple of ways to tackle this. One is to continue to use the
_Change() event and have it watch for changes to cells that are used in the
formulas in the cells that you do want to change color for. When a change
happens in one of those, you look at the ones that may need a color change
and see if it needs to be done.
The second way (possibly much busier) is to use the _Calculate() event and
check the values each time that event triggers.

I think you will find this discussion right on target with what you are
trying to do. Read through it completely because it ends up being a
situation like you are describing: change in cell in one column causes other
changes in other cells, and it is the other cells that need the format change:
http://www.microsoft.com/office/comm...2-576b132090f2

Good luck.


"MikeA" wrote:

I have used this code to work on a problem that I am having getting more the
3 colors out of Excel 2000. I am a code novice so please bear with me. I have
modified this code and it works great if I am entering data directly to the
worksheet. However, in my case the data is being brought to this sheet from
another based on a formula and the colors do not change when I update the
other sheet. How do I need to modify this code to make that work? Or do I
need to use a different event? If yes, how would the coding for that event
look like?

thanks

--
MikeA


"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks

Gladly,

Sheet1 is named "2nd Wave - Main Log"
Sheet2 is named "2nd Wave - Summary"

On the "2nd Wave - Main Log" sheet (sheet1), columns G, H, & I are labeled
DOC, Train, & Test respectively. The user would enter one of the 5 values
into these cells. Columns G, H, & I of rows 5 through 238 can have values
input. Let's talk about G133, H133, & I133 for this example.

On the "2nd Wave - Summary" sheet (sheet2), groups of 3 cells in column B
are labeled DOC, Train, & Test respectively. They start at B3 and go to B92.
For this example we will consider B51, B52, & B53.

When an entry is made to cell G133 (DOC) on sheet1, the same value appears
in cell C51 (in a row labled DOC) and this cell (C51) must change color based
on the value in G133 from sheet1.

When an entry is made to cell H133 (Train) on sheet1, the same value appears
in cell C52 (in a row labled Train) and this cell (C52) must change color
based on the value in H133 from sheet1.

When an entry is made to cell I133 (Test) on sheet1, the same value appears
in cell C53 (in a row labled Test) and this cell (C53) must change color
based on the value in H133 from sheet1.

On Sheet2, the range C3:P92 contain the cell that have to change color.

Cells in row 2 from C through P contain dates. The date in column C row 2
relates to the cells in column C from row 3 to 92, etc.

There is no logical relationship between where the values may be in the
range on sheet 1 and where the corresponding values appear on Sheet2. (No
down one row on sheet1 = across 1 column on sheet2).

If you need more info , let me know.

thanks for your time and effort.

MikeA

--
MikeA


"JLatham" wrote:

Ok, looks to me like we've added another level of complexity into things: a
second sheet!

Can you rewrite your explanation above in the following manner:

Provide the sheet name and address that will be changed by the user that
cause other cells to change, and then tell me which cells on which sheet(s)
need to change color, Kind of like this:
On Sheet1 user enters information into G, H or I (rows 5:238)
When value in G5:G238 changes, then on Sheet2 cells C5:C238 needs to change
(on same/corresponding row)

kind of like that - but I'm having a problem figuring out how changes in
columns G, H, I from rows 5 through 238 on Sheet1 correspond to columns C
through P and rows 3 through 92.

Perhaps even an example workbook sent to me as an attachment to an email?
With LOTS of explanation! If so, then send to (remove spaces)
HelpFrom @ jlathamsite.com



"MikeA" wrote:

Hi,
I have tried changing the code you referred me to to resolve my problem, but
have had no success. If I give you some details I was wondering if you could
suggest the proper code.

The data that is being entered that would trigger the change event is in
cells on sheet1 of the workbook. The cells that need to change colors are on
sheet2 of the same workbook.

On sheet 1 the data is in the range G5:I238. Each row is a project and
columns G, H, & I represent 3 task that have to be done for each project.
There are 5 codes that could go into any of these cells (IP, R, C, N/A, &
O/H) that represent the status.

Sheet2 is a summary and the values from sheet1 are pulled to cells on this
sheet using a simple formula that pulls the value forward and if the cell is
blank replaces the 0 with a blank (""). These cells on sheet2 need to change
colors based on the values from the cells on sheet1. These cells are in the
range C3:P92.

Data moves like this:

data from sheet1 cells G133, H133, I133 appear in sheets cells C51, C52, &
C53.

Since I started working on this they have added a new requirement. Sheet2
has a target Completion Date for each project in the range C3:P3. If todays
date is greater then that date they would like the cell color to be RED
rather then its usual color unless the code is "C" (completed).

I would greatly appreciate any help you could give me.

thanks

MikeA



--
MikeA


"JLatham" wrote:

Mike,
There's really not another place to get the code to act like that without
some kind of code change - as you've figured out.
Excel does not trigger the change event when the evaluation of a formula
results in a change.
There are a couple of ways to tackle this. One is to continue to use the
_Change() event and have it watch for changes to cells that are used in the
formulas in the cells that you do want to change color for. When a change
happens in one of those, you look at the ones that may need a color change
and see if it needs to be done.
The second way (possibly much busier) is to use the _Calculate() event and
check the values each time that event triggers.

I think you will find this discussion right on target with what you are
trying to do. Read through it completely because it ends up being a
situation like you are describing: change in cell in one column causes other
changes in other cells, and it is the other cells that need the format change:
http://www.microsoft.com/office/comm...2-576b132090f2

Good luck.


"MikeA" wrote:

I have used this code to work on a problem that I am having getting more the
3 colors out of Excel 2000. I am a code novice so please bear with me. I have
modified this code and it works great if I am entering data directly to the
worksheet. However, in my case the data is being brought to this sheet from
another based on a formula and the colors do not change when I update the
other sheet. How do I need to modify this code to make that work? Or do I
need to use a different event? If yes, how would the coding for that event
look like?

thanks

--
MikeA


"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks