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