Changing the color of a cell and adjacent cells based on conditions

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

I am creating a file to track the life cycle of our fleet vehicles. I
have the initial replacement year marked with a 1 to denote the first
life cycle period and then the cells are blank until the second
replacement year which is denoted with a 2.

I want to be able to find the life cycle of each unit and highlight
that specific life cycle a certain color for each vehicle in the same
vehicle class.

When I enter a 1 in the replacement year column, I want the input cell
and the five cells to the right of the input cell to be filled with
yellow. Can I solve this issue with conditional formatting or is there
a macro that I could use to do this? I want to avoid having to shade
this manually.

Any help would be greatly appreciated.

Below is an example of what the file looks like:

Equipment 2006 2007 2008 2009 2010
301798 1
301800 1
301807 1
301811 1
303411 1
 
You should use conditional formatting to achieve this. Use Formula is sio Cll
Value Is. Use the formula =($B2=1). Format the adjacent cells with the
painter. Repeat for the other cells as well. Obviously where you want to
use another column, substitute that column reference.
 
My only problem with that is that I have 2 to 3 life cycles to look at
for each vehicle and our fleet contains roughly 4,000 units. It would
be quite time consuming to use the format painter to make those
changes. I was looking for a way to automate this process with a macro
of some sort. Is there VBA code that can be used to select cells to
the right of the cell that contains the number and change the format of
all of them? I am not familiar with using VBA code. I have a book
that I have been looking through, but I haven't found exactly what I'm
looking for.

Any ideas would be very helpful.
 
Back
Top