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

--