Dynamic formatting based on the cell to the left

Hey guys,

Just wondering if anyone has any ideas on how to make a VBA formatting command that can change a cells colour based on the cell to the immediate left. The range I6:AM205 has a code in it that picks up specific codes placed in the cells to change the colour. However I need this colour to then spread all the way to the right of the range starting at the initial input. See below:

Please Login or Register  to view this content.

What I need is a way for every cell in the row after the effected cell to change to match. The only way I can think of doing this is something that picks up on a cell changing to black and the cell to its right then matches that change, so on and so on untill the end of the row but stopping at the limit of the range (being column AM). I have no idea how to get this done as anything I find online is based on matching one single cell, not a dynamic range.

Any help would be greatly appreciated, thanks in advance.

Hi,

Please take a look at my sample solution. Is this the sort of thing you were looking for? If so, just change the variable rng in my code from A1:J10 to I6:AM205 and the code should work fine in your workbook (I tried to make it fairly transportable code).

Let me know if there's anything else you need

P.S. If this solves your problem, please don't forget to mark this thread as solved (Thread Tools --> Mark As Solved), and please click on the * next to my post to say thanks

Hey ajryan,

Could you please post the code into the comment? Currently working on a network that doesn't allow downloads (or much of anything else really :P) Cheers for the quick reply though.

Please Login or Register  to view this content.

I knew there was a way to do this without VBA, it just required 2 seconds of thought instead of 1!!!

The attached workbook is the same as my previous attachment, but with a new worksheet added (called Non-VBA), which implements the same solution without VBA.

To implement this, I have highlighted cells A1:J10, and then selected Conditional Formatting from the Home tab on the Ribbon, and then chose "Use a formula to determine which cells to format", and entered this formula:

Formula:

Please Login or Register  to view this content.

The only potential issue here is that the condition has to be replicated for each potential value that you want to trigger the cell colouring event.

I hope one of my 2 solutions prove to be helpful

P.S. you might want to take a look at the second file when you get home and can download it...it is in my opinion a much neater solution than the VBA one.

Hmmm..

Can you please open the Intermediate Window in the VB editor (Ctrl + G), and type the following:

Please Login or Register  to view this content.

Press enter and let me know what that value is, and then type

Please Login or Register  to view this content.

and let me know what that value is. I suspect the error will have something to do with the assignment of rng or the context of Target.

Thanks

Both of those in the Intermediate Window window return with the "Object Required" error as well. Also if I link the macro to a button (as a test) Excel says impropper use of the "Me. Keyword". By the way, could this simply be a Excel 2003 problem?

Aha! The code needs to be pasted in a Worksheet module, not a standard Module.

Target is only valid as a range passed to the subroutine from the worksheet (and a standard Module doesn't represent a worksheet class), and the Me keyword is an equivalent way of pointing to the worksheet that the code is in (which is none if the code is in a standard Module).

I hope this clears things up a bit...

Based on your original code, does this help?

Please Login or Register  to view this content.

If someone decides to change back, we can remove the colour of the cells by putting an else clause here:

Please Login or Register  to view this content.

NB: I have not actually provided code because I don't know how much of my original code you have changed, but implementing the change that you require should be fairly simple...

Hi AJ,

Perfect. Here's the completed code.

Excel 2003 for some reason didn't like Target in the intermediate window, so I changed Target to Selection. The only other issue I had was the coloured cells extending to the end of the worksheet which was mitigated by changing "Me.Range()" to Columns + 2 at the end which I've tested extensively and has worked like a charm.

Can't tell you how much myself and Diablous89 appreciate the help here. We've had a lot of stabs at the cherry with our limited VBA experience but couldn't quite get there.

John: Thanks for your imput, we'll certainly play around with your code and see if we can't learn anything from it. ;-)

Please Login or Register  to view this content.

Oh silly mistake of mine with the Target/Selection thing...

The default Worksheet_Change subroutine takes Target as its Range argument (as in my first post, and John's code too), but I can now see that in your subroutine, it is actually taking Selection as its argument, so changing Target to Selection will work (I hadn't noticed this subtlety before). There is a disadvantage to this, because Selection is actually a reserved variable that refers to the currently selected range, which may not be the same as the Target. So you are actually limiting your ability to make use of this Selection variable for its intended purpose.

My suggestion would be to use Target as in my original code, and change the argument in the subroutine from Selection to Target, but if it is working for you, perhaps you're better off just leaving it alone for now.

I'm glad I could help you both Rob and Diablous.

Please don't forget to mark this thread as solved (Thread Tools --> Mark As Solved).

Have a great day