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

I don't think this is going to be very pretty. I'm going to print this out
and study it and see what can be done. My initial thought is that it is a
2-part, 2-code segment solution situation. Where we know there is a
relationship between cells on sheets [I'll use s1 for one sheet and s2 for
the other] as with s1!G133 and s2!C51, s1!H133 and s2!C52, and s1!I133 and
s2!C53 we can do things in code fairly easily and we can do them at the time
of the change to the cells in columns G, H and I.

Now the stuff on the second sheet is, shall we say, interesting. I
especially like the "there is no logical relationship between where the
values ... are on sheet 1 and ... where they appear on sheet2". This means,
to me at the moment, that we need a separate code associated with that second
sheet that goes through each of those cells and examines it's formula,
determines what cell it is getting its value from on the first sheet, checks
that value on the first sheet and then sets the color of the cell based on
that check. I think we can do that using the second sheet's _Activate()
event.

What that would mean is that, as far as the second sheet is concerned,
nothing happens on it while you are not looking at it and are working over in
the first sheet. But when you click on the second sheet to view it (activate
it), then it goes through it's code and updates the cells on it based on any
changes that may have been made back on the first sheet.

To quote Arnold when he was trying out for a high school play about the old
world music masters, "I'll be Bach!"...

"MikeA" wrote:

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

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

Is there ANY way you can attach the workbook to an email and send it to
HelpFrom @ jlathamsite.com
(remove spaces to get valid email).

The Subscript out of range means that most likely you have a sheet name
wrong, check the typing very closely, they have to be exactly alike,
especially with spaces. A mistake a lot of people make is that the actual
name on the tab may have an extra space at the beginning or end of the name,
but they don't use that in code (because they don't see it), and it can cause
that error.

If you have the code in the proper place, in the Worksheet_Activate()
routine (make sure you get it there by right-clicking the sheet's tab and
choosing [View Code] and paste the code into the sheet's code module), then I
don't see how the statement:

Set testRange = ActiveSheet.Range("C3:P92")

could fail. Virtually impossible. The ActiveSheet will be the one who's
activation caused the event to trigger, and every sheet has a range C3:P92.
Hard to fail.
Also, in the formula you've put up, you're missing a final "not true"
parameter somewhere. Not sure exactly what you want to do right now, but
I'll toss up a possible correction:

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1, "" , "NEITHER TRUE"))

"MikeA" wrote:

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

The email in on the way.

thanks

Mike
--
MikeA


"JLatham" wrote:

Is there ANY way you can attach the workbook to an email and send it to
HelpFrom @ jlathamsite.com
(remove spaces to get valid email).

The Subscript out of range means that most likely you have a sheet name
wrong, check the typing very closely, they have to be exactly alike,
especially with spaces. A mistake a lot of people make is that the actual
name on the tab may have an extra space at the beginning or end of the name,
but they don't use that in code (because they don't see it), and it can cause
that error.

If you have the code in the proper place, in the Worksheet_Activate()
routine (make sure you get it there by right-clicking the sheet's tab and
choosing [View Code] and paste the code into the sheet's code module), then I
don't see how the statement:

Set testRange = ActiveSheet.Range("C3:P92")

could fail. Virtually impossible. The ActiveSheet will be the one who's
activation caused the event to trigger, and every sheet has a range C3:P92.
Hard to fail.
Also, in the formula you've put up, you're missing a final "not true"
parameter somewhere. Not sure exactly what you want to do right now, but
I'll toss up a possible correction:

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1, "" , "NEITHER TRUE"))

"MikeA" wrote:

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

The email in on the way.

thanks

Mike
--
MikeA


"JLatham" wrote:

Is there ANY way you can attach the workbook to an email and send it to
HelpFrom @ jlathamsite.com
(remove spaces to get valid email).

The Subscript out of range means that most likely you have a sheet name
wrong, check the typing very closely, they have to be exactly alike,
especially with spaces. A mistake a lot of people make is that the actual
name on the tab may have an extra space at the beginning or end of the name,
but they don't use that in code (because they don't see it), and it can cause
that error.

If you have the code in the proper place, in the Worksheet_Activate()
routine (make sure you get it there by right-clicking the sheet's tab and
choosing [View Code] and paste the code into the sheet's code module), then I
don't see how the statement:

Set testRange = ActiveSheet.Range("C3:P92")

could fail. Virtually impossible. The ActiveSheet will be the one who's
activation caused the event to trigger, and every sheet has a range C3:P92.
Hard to fail.
Also, in the formula you've put up, you're missing a final "not true"
parameter somewhere. Not sure exactly what you want to do right now, but
I'll toss up a possible correction:

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1, "" , "NEITHER TRUE"))

"MikeA" wrote:

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

Email and workbook received, problem analyzed, fix applied, returned to you <g

Problem, for those reading this, was that I wasn't aware that some of the
cells in the C3:P92 range would have anything other than a simple reference
back to a cell on the other sheet. But that's what was happening - instead
of a simple
='Sheet 1'!X99
type of reference, several locations had an =IF('Sheet 1'!Y240, type of
formula in them. This threw the 'parser' that dug out the sheet name and
cell address into a tizzy and caused the error. All fixed now, works with
current setup, waiting for a different formula to come along and hose it down
once more.

"MikeA" wrote:

The email in on the way.

thanks

Mike
--
MikeA


"JLatham" wrote:

Is there ANY way you can attach the workbook to an email and send it to
HelpFrom @ jlathamsite.com
(remove spaces to get valid email).

The Subscript out of range means that most likely you have a sheet name
wrong, check the typing very closely, they have to be exactly alike,
especially with spaces. A mistake a lot of people make is that the actual
name on the tab may have an extra space at the beginning or end of the name,
but they don't use that in code (because they don't see it), and it can cause
that error.

If you have the code in the proper place, in the Worksheet_Activate()
routine (make sure you get it there by right-clicking the sheet's tab and
choosing [View Code] and paste the code into the sheet's code module), then I
don't see how the statement:

Set testRange = ActiveSheet.Range("C3:P92")

could fail. Virtually impossible. The ActiveSheet will be the one who's
activation caused the event to trigger, and every sheet has a range C3:P92.
Hard to fail.
Also, in the formula you've put up, you're missing a final "not true"
parameter somewhere. Not sure exactly what you want to do right now, but
I'll toss up a possible correction:

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1, "" , "NEITHER TRUE"))

"MikeA" wrote:

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

Wow,
I have been looking through these discussion groups for weeks trying to come
up with a solution to my problem and this thread is the closest I have come
to the biggest headache I have had... color format!
I was able to use code out of:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
..to get one of my sheets to work, but it won't resolve for my
Vlookup\Index\Reference formula because this VBA counts on just an off page
ref.

May I ask, was your accounting for a formula difficult to obtain the desired
results?
My issue is that I want to color a cell based on a status in another
worksheet (that I don't carry to my active worksheet)
For instance
Tracking sheet contains:
a change number (A3:A1500)
Summery (D3:D1500)
date (E3:E1500)
time (F3:F1500)
implementor (I3:I1500)
Status (K3:K1500)

I then do a carry over to another page (formatted) to sort this data in the
columns I want for a Vlookup, ie formatted! A1='Tracking'!E3 (so that I can
lookup by date) then formatted! B1='Tracking'!F3, etc.

Then I have this ghetto Calendar on the next sheet called "Calendar"
It looks like one month at a glance and each "Date cell" is made up of 4
columns x 20 rows (to account for mulitiple actions in one day). Formula is:
{=INDEX(formatted!$A$1:$E$1999,SMALL(IF(formatted! $A$1:$A$1999=39093,ROW(formatted!$A$1:$A$1999)),RO W(formatted!1:1)),2)}

(fyi, 39093 is the date of 1/11/07)
I have conditional format take out the #NUM when it does not find a ref.
Now, while I don't want to see "status"(ie. Tracking sheet! K3:K1500) at a
glance on Calendar!, I would like my summery cell (column ref 5 of
formatted!) highlighted green if status = approved and red of status =
cancelled

Wow, I hope someone can understand all that!

Any help would be WONDERFUL!

-Britt

"JLatham" wrote:

Email and workbook received, problem analyzed, fix applied, returned to you <g

Problem, for those reading this, was that I wasn't aware that some of the
cells in the C3:P92 range would have anything other than a simple reference
back to a cell on the other sheet. But that's what was happening - instead
of a simple
='Sheet 1'!X99
type of reference, several locations had an =IF('Sheet 1'!Y240, type of
formula in them. This threw the 'parser' that dug out the sheet name and
cell address into a tizzy and caused the error. All fixed now, works with
current setup, waiting for a different formula to come along and hose it down
once more.

"MikeA" wrote:

The email in on the way.

thanks

Mike
--
MikeA


"JLatham" wrote:

Is there ANY way you can attach the workbook to an email and send it to
HelpFrom @ jlathamsite.com
(remove spaces to get valid email).

The Subscript out of range means that most likely you have a sheet name
wrong, check the typing very closely, they have to be exactly alike,
especially with spaces. A mistake a lot of people make is that the actual
name on the tab may have an extra space at the beginning or end of the name,
but they don't use that in code (because they don't see it), and it can cause
that error.

If you have the code in the proper place, in the Worksheet_Activate()
routine (make sure you get it there by right-clicking the sheet's tab and
choosing [View Code] and paste the code into the sheet's code module), then I
don't see how the statement:

Set testRange = ActiveSheet.Range("C3:P92")

could fail. Virtually impossible. The ActiveSheet will be the one who's
activation caused the event to trigger, and every sheet has a range C3:P92.
Hard to fail.
Also, in the formula you've put up, you're missing a final "not true"
parameter somewhere. Not sure exactly what you want to do right now, but
I'll toss up a possible correction:

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1, "" , "NEITHER TRUE"))

"MikeA" wrote:

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

Britt,
This is probably lost to the world stuck this deep into a discussion of
another person's problem. Plus it's going to get 'nasty' if we keep passing
notes through it - as in scrolling off to the right in my display.

Might have been better to have started a new question with a reference to
this discussion.

I have a difficult time sometimes in envisioning what's going on in complex
situations like this. Would it be possible for you to send me copies of the
two workbooks involved as email attachments? If so, attach them to an email
and send it to (remove all spaces)
Help From @ jlathamsite.com
and it'll get to me.

With the other workbook closed, it's difficult to determine the
content/status of a particular cell in it, and VLOOKUP() doesn't work across
closed workbooks anyhow. But given both books, I can look to see what kind
of solution we can come up with, it might involve a hidden sheet with links
to the closed workbook just echoing the values of cells that your color
coding needs to be based on. Can't say for sure without seeing it all.

"Britt" wrote:

Wow,
I have been looking through these discussion groups for weeks trying to come
up with a solution to my problem and this thread is the closest I have come
to the biggest headache I have had... color format!
I was able to use code out of:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
.to get one of my sheets to work, but it won't resolve for my
Vlookup\Index\Reference formula because this VBA counts on just an off page
ref.

May I ask, was your accounting for a formula difficult to obtain the desired
results?
My issue is that I want to color a cell based on a status in another
worksheet (that I don't carry to my active worksheet)
For instance
Tracking sheet contains:
a change number (A3:A1500)
Summery (D3:D1500)
date (E3:E1500)
time (F3:F1500)
implementor (I3:I1500)
Status (K3:K1500)

I then do a carry over to another page (formatted) to sort this data in the
columns I want for a Vlookup, ie formatted! A1='Tracking'!E3 (so that I can
lookup by date) then formatted! B1='Tracking'!F3, etc.

Then I have this ghetto Calendar on the next sheet called "Calendar"
It looks like one month at a glance and each "Date cell" is made up of 4
columns x 20 rows (to account for mulitiple actions in one day). Formula is:
{=INDEX(formatted!$A$1:$E$1999,SMALL(IF(formatted! $A$1:$A$1999=39093,ROW(formatted!$A$1:$A$1999)),RO W(formatted!1:1)),2)}

(fyi, 39093 is the date of 1/11/07)
I have conditional format take out the #NUM when it does not find a ref.
Now, while I don't want to see "status"(ie. Tracking sheet! K3:K1500) at a
glance on Calendar!, I would like my summery cell (column ref 5 of
formatted!) highlighted green if status = approved and red of status =
cancelled

Wow, I hope someone can understand all that!

Any help would be WONDERFUL!

-Britt

"JLatham" wrote:

Email and workbook received, problem analyzed, fix applied, returned to you <g

Problem, for those reading this, was that I wasn't aware that some of the
cells in the C3:P92 range would have anything other than a simple reference
back to a cell on the other sheet. But that's what was happening - instead
of a simple
='Sheet 1'!X99
type of reference, several locations had an =IF('Sheet 1'!Y240, type of
formula in them. This threw the 'parser' that dug out the sheet name and
cell address into a tizzy and caused the error. All fixed now, works with
current setup, waiting for a different formula to come along and hose it down
once more.

"MikeA" wrote:

The email in on the way.

thanks

Mike
--
MikeA


"JLatham" wrote:

Is there ANY way you can attach the workbook to an email and send it to
HelpFrom @ jlathamsite.com
(remove spaces to get valid email).

The Subscript out of range means that most likely you have a sheet name
wrong, check the typing very closely, they have to be exactly alike,
especially with spaces. A mistake a lot of people make is that the actual
name on the tab may have an extra space at the beginning or end of the name,
but they don't use that in code (because they don't see it), and it can cause
that error.

If you have the code in the proper place, in the Worksheet_Activate()
routine (make sure you get it there by right-clicking the sheet's tab and
choosing [View Code] and paste the code into the sheet's code module), then I
don't see how the statement:

Set testRange = ActiveSheet.Range("C3:P92")

could fail. Virtually impossible. The ActiveSheet will be the one who's
activation caused the event to trigger, and every sheet has a range C3:P92.
Hard to fail.
Also, in the formula you've put up, you're missing a final "not true"
parameter somewhere. Not sure exactly what you want to do right now, but
I'll toss up a possible correction:

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1, "" , "NEITHER TRUE"))

"MikeA" wrote:

Jerry,

Thanks for all of your help, but I am still having a problem.

I copied the code into the Active event of sheet 2, but when I go to that
tab I get a Run Time error "9", Script out of Range. The VB debug screen
(yellow line) points to the 1st cell in the range C3. I think the problem
relates to the formula I use in the cells. When I change it to just the cell
reference the logic accepts it and stops on the next cell.

Using cell reference returns a (zero) if there is nothing in the cell on
sheet 1. The user wants it to be blank. The following formula was used to do
this.

=IF('2nd Wave - Main Log'!G290,'2nd Wave - Main Log'!G29,IF('2nd Wave -
Main Log'!G29<1,""))

Can the code we modified to account for this or is there a better way for me
to accomplish the users request?
____

As to the date request.

If any cell on sheet 2, column C, range C3:C92 reflects a value other then
"C" (R, IP, N/A, O/H) AND the date in cell C2 is less then today, turn that
cell RED rather then the color normally dictated by the code. This would also
apply to columns D through P

thanks

Mike
--
MikeA


"JLatham" wrote:

MikeA, download this file:
http://www.jlathamsite.com/uploads/MikeA_v1.xls
and see where we are with this. I didn't set all the cells up on the second
page with links back to the first, just a couple of groups, but that's enough
to show how it works.
Still have questions about your needs in C2:P2 on the second sheet.


"MikeA" wrote:

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

--