Copy conditional formatting with relative data

**Sophie10** · 01-13-2015, 10:31 AM

Hello,

I'm hoping someone here can help me out...

See attachment.
If 'time used' is >= 'time available', the cell 'time used' has to be colored red
If 'time used' is 0, the cell 'time used' has to be colored green
Everything in between 0 and 'time available' has to be colored between green and red (yellow, orange,…)

I’m able to do this cell by cell but it seems to be impossible to copy the conditional formatting to other cells because it always seems to refer to A2 instead of A3, A4,…

I have more than 200 lines like this so manual would take ages.

Can someone maybe tell me how to do this?

Thanks a lot in advance!

Sophie

**Ace_XL** · 01-13-2015, 10:34 AM

Remove absolute references (the $ signs) in your CF formulas

**Sophie10** · 01-13-2015, 10:42 AM

Thanks for your quick reply.
Actually I don't use a formula but rather a number and a value. See image below:
excel.jpg

When I remove the absolute references in that value-field, I get an error message.

I also notice that I'm doing something wrong because I notice that how I do it now, only 3 colors are used (green, yellow and red), no orange or variations like you normally see with conditional formatting.
So I guess I'm not doing that the correct way either...

Any more advice?

Thanks in advance!

**Sophie10** · 01-13-2015, 10:59 AM

In attachment you can find a sample file.

**Ace_XL** · 01-13-2015, 11:04 AM

See attached. I used 3 CF rules and extended the range to cover B2:B6

You may choose to delete the first two rules

**Sophie10** · 01-13-2015, 11:16 AM

Thanks but it's not completely yet what I have in mind.
The CF of the B-cells still refer to A2 while it has to be rather like this:

- B2 refers to A2, B3 refers to A3, B4 refers to A4,...
- And I need a way to copy that CF to 200 lines or so...

Do you understand what I mean?

**Ace_XL** · 01-13-2015, 11:40 AM

Nah..not sure you can do that. A bit of a workaround although not ideal..

Use a helper column, which will eventually end up being formatted since the graded scale does not allow other ranges to be formatted.

See attached. Col C is the helper column being formatted with the 3 CF rules

**Sophie10** · 01-13-2015, 11:50 AM

Thanks a lot for your help! I can live with the helper column on itself but it doesn't seem to work either for the following reasons:

- The colors still aren't like I want them. For example C2 should be dark orange instead of yellow, C5 should be yellow instead of red and C3 should be red instead of green (preferably not as red as when 'time used' > 'time available')...
- It's also not ideal that some cells in column C have numbers in it and some not...

Maybe you or someone else has another suggestion?

Copy conditional formatting with relative data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Re: Copy conditional formatting with relative data

Thread Information

Users Browsing this Thread

Similar Threads

Conditional Formatting using Data Bars giving me Relative Reference Error

Conditional formatting with a relative cell.

Excel 2007 : Relative reference conditional formatting

[SOLVED] Conditional Formatting with Relative Reference.

Conditional Formatting Bug w/ relative formula?

Bookmarks

Bookmarks

Posting Permissions