Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

User Tag List

Conditional Format In A Range, based on formula

This is a discussion on Conditional Format In A Range, based on formula within the Microsoft Office support forums, part of the Tech Support Forum category. The format is applied in Cell A2 and is based on a formula (C2+D2)>=0 I can get this to work


Closed Thread
 
Thread Tools Search this Thread
Old 11-21-2016, 08:25 AM   #1
TSF Enthusiast
 
tomohawk's Avatar
 
Join Date: Dec 2009
Location: Dublin, Ireland
Posts: 1,241
OS: Windows 10 Pro (64-bit)



The format is applied in Cell A2 and is based on a formula (C2+D2)>=0

I can get this to work fine for one cell, but I want to apply it down as far as cell A28 (for values in C28 and D28).

Is there an easy way to do this?

T
tomohawk is offline  
Sponsored Links
Advertisement
 
Old 11-21-2016, 06:36 PM   #2
Microsoft MVP
Microsoft Team Moderator
Articles Team Manager
Hardware Team Moderator
 
Masterchiefxx17's Avatar

Microsoft Most Valuable Professional
 
Join Date: Feb 2010
Location: Wisconsin, USA
Posts: 39,471
OS: Windows 10 Professional 64bit

My System


Select the formula and then drag a selection window down to cell A28. There should be a small box at the bottom right of your selection. Double click it and it should apply the formula.
Masterchiefxx17 is offline  
Old 11-22-2016, 02:41 AM   #3
TSF Enthusiast
 
tomohawk's Avatar
 
Join Date: Dec 2009
Location: Dublin, Ireland
Posts: 1,241
OS: Windows 10 Pro (64-bit)



Thanks for your support Masterchiefxx17

I have put the Conditional format in cell A2 (=$E$2>0), it makes all references absolute.

When I select a range A2:A28, a "Quick Analysis" box appears at the bottom-right. When I double click on it, nothing happens.

When I click on it once, I get a list under a heading "Formatting" (Text, Duplicate, Unique, Equal To, Clear)

I hovered the cursor over "Duplicate", but no changes were displayed.

T
tomohawk is offline  
Sponsored Links
Advertisement
 
Old 11-22-2016, 05:04 AM   #4
TSF Enthusiast
 
tomohawk's Avatar
 
Join Date: Dec 2009
Location: Dublin, Ireland
Posts: 1,241
OS: Windows 10 Pro (64-bit)



I've also selected the range A2:A28 and when I get the cross-hair at the bottom-right, I double-clicked on it. The result was that it copied cells A2:A4 into cells A29:A31. Very Strange!!!



Quote:
Originally Posted by tomohawk View Post
Thanks for your support Masterchiefxx17

I have put the Conditional format in cell A2 (=$E$2>0), it makes all references absolute.

When I select a range A2:A28, a "Quick Analysis" box appears at the bottom-right. When I double click on it, nothing happens.

When I click on it once, I get a list under a heading "Formatting" (Text, Duplicate, Unique, Equal To, Clear)

I hovered the cursor over "Duplicate", but no changes were displayed.

T
tomohawk is offline  
Old 11-22-2016, 02:02 PM   #5
Microsoft MVP
Microsoft Team Moderator
Articles Team Manager
Hardware Team Moderator
 
Masterchiefxx17's Avatar

Microsoft Most Valuable Professional
 
Join Date: Feb 2010
Location: Wisconsin, USA
Posts: 39,471
OS: Windows 10 Professional 64bit

My System


Is this what you are looking for?

https://www.pryor.com/blog/copy-exce...fill-a-column/
Masterchiefxx17 is offline  
Old 11-22-2016, 02:23 PM   #6
TSF Enthusiast
 
tomohawk's Avatar
 
Join Date: Dec 2009
Location: Dublin, Ireland
Posts: 1,241
OS: Windows 10 Pro (64-bit)



Thanks for the reply Masterchiefxx17

I attach a test file, and I have set up a conditional format in cell A2 (using a formula based on the value recorded in cell E2). I want to apply the same format in all the cells now from A3 to A28, but I can't find a quick way to do it, other than setting up individually for each cell. There has to be a quicker way. I don't know why conditional formatting uses absolute references (in both the formulas and in the formattable cell), but I'm sure there is a good reason.

T
Attached Files
File Type: zip CF_Example.zip (8.3 KB, 8 views)
tomohawk is offline  
Old 11-24-2016, 08:45 PM   #7
Microsoft MVP
Microsoft Team Moderator
Articles Team Manager
Hardware Team Moderator
 
Masterchiefxx17's Avatar

Microsoft Most Valuable Professional
 
Join Date: Feb 2010
Location: Wisconsin, USA
Posts: 39,471
OS: Windows 10 Professional 64bit

My System


Just like this, right?

Masterchiefxx17 is offline  
Old 11-25-2016, 01:34 AM   #8
TSF Enthusiast
 
tomohawk's Avatar
 
Join Date: Dec 2009
Location: Dublin, Ireland
Posts: 1,241
OS: Windows 10 Pro (64-bit)



Hi Masterchiefxx17

Thanks for the reply. I was not aware of the double-click option and that's handy to know.

I tried this on the sample spreadsheet (attached again) but it does not seem to work. It simply copies the formatting (but not the condition) in cell A2 all the way down.

T
Attached Files
File Type: zip CF_Example.zip (8.4 KB, 5 views)
tomohawk is offline  
Old 11-25-2016, 02:42 AM   #9
TSF Enthusiast
 
Join Date: Aug 2015
Posts: 900
OS: Win 7 HP 64B SP1



Quote:
Originally Posted by tomohawk View Post
I've also selected the range A2:A28 and when I get the cross-hair at the bottom-right, I double-clicked on it. The result was that it copied cells A2:A4 into cells A29:A31. Very Strange!!!
Take the $ out. Absolute means just that.

Adding the $ ($A1*$B1) makes all cells display the result of (A1*B1) no matter where you paste it.

(A1*B1) will display that result in cell C1. Copying/Pasting that to C2 will display the results of (A2*B2). Pasting it in C3 will display the result of (A3*B3) and so on down the column. Doing that, you can select a range of cells to paste to and they will all display the result of (Ax*Bx) as far down as you paste into Cx



I do that in a table of monthly temperature ranges where I want to calculate information based on the high and low for the day.
Confounded Also is offline  
Old 11-25-2016, 03:23 AM   #10
TSF Enthusiast
 
tomohawk's Avatar
 
Join Date: Dec 2009
Location: Dublin, Ireland
Posts: 1,241
OS: Windows 10 Pro (64-bit)



Thanks for the support Confounded Also

In the Conditional Format rule, the "Applies To" (the area to be formatted) cannot be changed from an Absolute Reference.

I have changed the reference in the "Formula" field to Relative.

Using Masterchiefxx17's advice regarding double-clicking on the cross-hair at the bottom right of the cell to be copied, this now works.

Thanks to both of you.

T
tomohawk is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
BSOD during hibernation (error code 9F)
Hello everyone, I have been experiencing BS error for quite sometime probably after installing mSATA card. I got my mainboard, rams and PSU replaced a week ago but to no avail. Its a DELL XPS 8500 with WIN 7 on it, is there someone who could help me with it. I do have a dump file saved for...
farri BSOD, App Crashes And Hangs 27 02-09-2013 05:07 PM
BSOD DRIVER_POWER_STATE_FAILURE
I just got 1 BSOD and am curious about the reasons of that. Any help will be appreciated. I attached the dump file and the screenshot of the dump. Thanks for all your help.
erdem84 BSOD, App Crashes And Hangs 5 10-24-2012 07:05 AM
[SOLVED] Excel 2007 complex Conditional format
I want to fill only certain cells with a color; the color to use is determined by the value in a cell outside the range of cells to fill. Spreadsheet data is a8:ap46; cells to fill are g8:ap46. I want to fill only "nonblank cells" in g8:ap46 with green, blue, or yellow. If value in column C...
SamMd Microsoft Office support 2 02-06-2011 03:17 PM

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2
Powered by vBadvanced CMPS v3.2.3


All times are GMT -7. The time now is 01:57 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2020, vBulletin Solutions, Inc.
vBulletin Security provided by vBSecurity v2.2.2 (Pro) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.1.0 (Pro) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
Copyright 2001 - 2018, Tech Support Forum

Windows 10 - Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts