I need a little sub routine

[hshayhorn]

I have a worksheet that I need to validate and change cells in a row based on
the value of one of the cells within that row. For example. If A3=Blue then
A5, A7, A8 and A10 would be colored GREY. I can handle that but what I'm
having trouble with is I need something that will call that same routine for
A4, A5, A6, A7 and so on... The value in A no matter what the row controls
whether or not the same cells in their respective column is greyed out or
now. I don't want to have to write hundreds of these routines I'm hoping for
just one that will understand that I'm only dealing with the row the user
just ented data in.

I hope that was enough informaiton.

 

[Gleam]

I think that what you want to do in logic terms is:
Row=3
If A:Row="BLUE" then
Colour these cells grey : A:Row+2, A:Row+4, A:Row+5,A:Row+7
end if

The trouble is that this logic breaks down for the second example.

I think that activecell.row may be the starting point for some code that is
applicable for many starting points.

HTH

 

[Rick Rothstein]

Can you clarify your description? You say if A3=Blue, then make A5, A7, A8
and A10 grey, but then go on to test A5, A7, etc. for being blue. Did you
mean the A5, A7, A8 and A10 to be columns by any chance (and if yes, which
ones)?

 

[hshayhorn]

OK, let me see if I can clarify this better. I was worried it wouldn't be
very clear.
I have a sheet with several columns. Depending on the value in column A of
each row I need to change the color of other cells or columns in that row.
The change in color needs to be for that row only though. So...

Example

If A1 = "Purple" then B1, D1, G1 and I1 the cell shading or color for those
cells is changed to grey

Now, if A2 = "Blue" then nothing changes

Then if A3 = "Purple" then B3, D3, G3 and I3 the cell shading or color for
those cells is changed to grey.

I hope this is clearer. I did make a mistake in my earlier post. Thanks for
catching it.

 

[Rick Rothstein]

One more question. Is the only color name that will force a change in B1,
D1, G1 and I1's cell shading the word "Purple"? That is, am I correct in
assuming *any* other text (whether the word "Blue" or not) will make it so
there is no cell shading in those cells?

 

[hshayhorn]

I'm actually using colors as a general way of explaining what I need to do.
There actually could be ike three possible values.

Value 1 changes A1, B1, E1 and F1
Value 2 changes C1, D1, G1
Value 3 changes nothing

Like I said though. Each row is a new chance for those values to appear and
change the cells in it's row.

 

[Rick Rothstein]

I've never understood why people posting questions on newsgroups think that
it will be little trouble taking the answer to a highly simplified question
which they do not know how to solve and somehow be able to expand it into
the answer for their actual question. It really does not work that way in
the majority of cases. You say you are using colors as a "general way of
explaining what [you] need to do"... what is it that you actually want to do
to those cells? There is a good chance that the solution to what you asked
will in no way be applicable to what you actually want, depending, of
course, what that is. Also, you say there could be "like three possible
values". Could there be more? If so, how many (3 is a maximum limit in the
solution I am thinking may apply)? The values you are talking about... they
are all going to still be in column A, right?

 

[Mike Fogleman]

If NOT MyQuestion = MyProblem Then
YourAnswer <> MySolution
Else
MySolution = NOT Solved
End If

Mike F

I've never understood why people posting questions on newsgroups think
that it will be little trouble taking the answer to a highly simplified
question which they do not know how to solve and somehow be able to expand
it into the answer for their actual question. It really does not work that
way in the majority of cases. You say you are using colors as a "general
way of explaining what [you] need to do"... what is it that you actually
want to do to those cells? There is a good chance that the solution to
what you asked will in no way be applicable to what you actually want,
depending, of course, what that is. Also, you say there could be "like
three possible values". Could there be more? If so, how many (3 is a
maximum limit in the solution I am thinking may apply)? The values you are
talking about... they are all going to still be in column A, right?

--
Rick (MVP - Excel)

I'm actually using colors as a general way of explaining what I need to
do.
There actually could be ike three possible values.

Value 1 changes A1, B1, E1 and F1
Value 2 changes C1, D1, G1
Value 3 changes nothing

Like I said though. Each row is a new chance for those values to appear
and
change the cells in it's row.

 

[Mike Fogleman]

Would it be possible to use Conditional Formatting in columns B thru G to do
what you need? Or do you just want it done by code rather than on the
worksheet with built-in features?

Mike F

 

[hshayhorn]

Rick, I am telling you exactly what I need to do I'm just changing my actual
values from what they are to the word "purple" or "blue" the actual values
are unimportant. The other cells still need to change color based on a
certain value.

I've never understood why people posting questions on newsgroups think that
it will be little trouble taking the answer to a highly simplified question
which they do not know how to solve and somehow be able to expand it into
the answer for their actual question. It really does not work that way in
the majority of cases. You say you are using colors as a "general way of
explaining what [you] need to do"... what is it that you actually want to do
to those cells? There is a good chance that the solution to what you asked
will in no way be applicable to what you actually want, depending, of
course, what that is. Also, you say there could be "like three possible
values". Could there be more? If so, how many (3 is a maximum limit in the
solution I am thinking may apply)? The values you are talking about... they
are all going to still be in column A, right?

--
Rick (MVP - Excel)

I'm actually using colors as a general way of explaining what I need to
do.
There actually could be ike three possible values.

Value 1 changes A1, B1, E1 and F1
Value 2 changes C1, D1, G1
Value 3 changes nothing

Like I said though. Each row is a new chance for those values to appear
and
change the cells in it's row.

 

[hshayhorn]

Is it possible to type into conditional formatting a statement like:

=B5="Blue" and C5="Red" or B5=Purple then color the cell grey?

I need both the first condiitons to be true or the third one to be true in
order for the cells color to change.

 

[Mike Fogleman]

Yes, but this example is not consistent with your earlier example.

Mike F