Conditional Format of cells based on other cell values

**hamidxa** · 05-18-2015, 12:42 AM

Hi,

I have a worksheet that contains 4 descriptive columns (X, Y, Z, AA) which contain text values "green", "yellow", "red" whose values correspond to 4 other columns that hold time-based values (Columns N, O, P, Q)

What I am trying to accomplish is as follows:

If the text value "green" appears in Col X, then the color green should be applied to Col N, else if the value "yellow" appears in Col X, then the color yellow should be applied to Col N, else if the value "red" appears in Col X, then the color red should be applied to Col N

The same relationship described above (for Col X and N) with the same conditions exists and should be similarly applied for the following column pairs:
Y & O (ex: so if Y4 holds the value "green", then O4 should be colored green)
Z & P (ex: if Z4 holds the value "yellow", then P4 should be colored yellow)
AA & Q (ex: if AA4 holds the value "red", then Q4 should be colored red)

I've attached a workbook with a few sample rows of data, and have applied formatting by hand to the first 5 rows of data to hopefully better illustrate what I am looking to do.

The final workbook would be several thousand rows long, and I already have a lot of macros tied to it, so I would love to be able to wrap it up with one final formatting macro.

I would be truly grateful for any help offered.

Thanks in advance!

**RedSummer** · 05-18-2015, 12:51 AM

Hey,
It seems like you just need normal conditional formatting
with a formula that says "=X4="red""
and another rule saying "=X4="green""
and so on.
I'm attaching this, I'm colorblind so you may want to correct the colors! :D
cftest.xlsx

I don't think you need VBA at all.
Cheers,
Red

**hamidxa** · 05-18-2015, 12:58 AM

Hi Red,

Thanks for sharing this approach.

I took a similar one when going the non vba route, yet remain very interested in exploring a vba based approach.

Would love to see if a vba macro could accomplish this more efficiently and also work with the rest of my macros.

**RedSummer** · 05-18-2015, 01:16 AM

Now personally, other than a macro that will set the Conditional Formats that you can initially put in by hand, an if statement for each cell going over the possibilities of green, red, yellow for 4 cells a row, thousands a row, would probably be a slow running macro. If you want the code for the conditional formatting in vba form let me take a look for a quick second, I won't get the colors right but I'll give you the skeleton!

**RedSummer** · 05-18-2015, 01:50 AM

I believe you have green red and yellow there from formulas that list them based on criteria rather than typing them out for thousands of rows. It might save you more time to stick those arguments in the conditional formatting arguments to start.
The code I was working on isn't really working but i'll post the general idea, someone else can fix it since i'm actually pretty terrible with VBA XD and it's around 2 am here ~

Please Login or Register  to view this content.

Best of luck to you!

**hamidxa** · 05-18-2015, 08:29 AM

Red,

That is correct. I have already built out the conditions for red, yellow, green through a series of IF statements, so that part is essentially done.

Your concept code is pretty much what I'm going after, and remain interested in seeing if this could be further adapted to my example.

**JOHN H. DAVIS** · 05-18-2015, 08:49 AM

Does this help?

Please Login or Register  to view this content.

**RedSummer** · 05-18-2015, 10:44 AM

That's some reallllly nice stuff there Mr. Davis!

**JOHN H. DAVIS** · 05-18-2015, 11:10 AM

Originally Posted by RedSummer

That's some reallllly nice stuff there Mr. Davis!

Thanks for the compliment.

**hamidxa** · 05-18-2015, 12:05 PM

John,

This is awesome!
Thank you so much for your help, really appreciate it!

**JOHN H. DAVIS** · 05-18-2015, 01:02 PM

You're welcome. Glad to help out and thanks for the feedback.

Conditional Format of cells based on other cell values

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Re: Conditional Format of cells based on other cell values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional format a cell based on cumulative value of a row of cells

[SOLVED] Conditional Format color of cell based on values in a range of cells

[SOLVED] Conditional format for duplicate values based on criteria in a different cell

[SOLVED] Conditional Format cell based on value of cells in range

Conditional Format 1 cell based on other cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions