Conditional Formatting With Reference To The Whole Column
Jun 29, 2006
I have made a sample file with conditional formatting. The file contains 3 sheets which contain a number of orders. Conditional formatting is changing colour of the whole line depending on value in column C. The formatting contains reference to a certain cell in column C. It is very easy to make when the lists are so short: I make conditional formatting for line 2 with reference to cell C2, copy formats in the other 2 lines and change formula references to C3 and C4. However in the original spreadsheet there might be more than 5000 lines per sheet and it will be too time consuming to correct the formulas manually. Does anyone know how I can make a reference to column C without specifying each particular cell?
View 3 Replies
ADVERTISEMENT
Jun 3, 2014
i want to calculate percentage between A and B and highlight top 10 values in B.
as of now i am calculating % in C and applied conditional formatting in C. filtering C based on color i am formatting B manually.
is there any option to avoid this manual work.
Total countAlarm reported%
562545%
783545%
322578%
View 3 Replies
View Related
Jul 24, 2014
I'm using conditional formatting where one column of cells is referencing another column of cells. For example, I want cell K2 to highlight if the date entered doesn't match the date in F2. I then want cell K3 to highlight if the date entered doesn't match the date in F3. I want cell K4 to highlight if the date entered doesn't match the date in F4 and so on. I'm not having any issue putting conditional formatting on the cells/row references individually, but when I try to copy the formatting (by using format painter) to save time all of the cells in column K just reference the first cell in column F since the first cell in column K references that F cell but not any other F cell. How do I copy these row by row references between columns so that each reference is unique?
View 1 Replies
View Related
Feb 28, 2013
I have a row of data I want to be blacked out if there isnt a reference number in the first column
So if I type the reference number i can in theory then use the rest of the row but if there is no reference number in the first column i cant see any of the row ...
View 1 Replies
View Related
Dec 21, 2008
As shown in the image given below, I have 2 tables. In Image 1, Vacation details are being fetched from a different worksheets. There can be 13 possible values in a particular cell. These 13 entries are,
V
SL
CO
T
PH
W
ML
PL
AD
BE
CTW
EM
LOP
MAR
OTR
Excel Jeanie HTMLSheet2 *BCDEFGHIJKLMNOP2Feb-09*Feb-093MonTueWedThuFriSatSun*MonTueWedThuFriSatSun4**************15VSLCOTPH***23456786WMLPLADBE***91011121314157EMLOPMAROTR****161718192021228CTW*******232425262728*9*************** Excel tables to the web >> Excel Jeanie HTML 4
WHAT I NEED :
If the value in cell B5 is V then J5 is being colored with PINK background. Similarly if any cell in Image 1 has value SL ( for e.g. C5 ) then K5 is being colored with orange. Similarly there should be 13 different types of color coding for all the above values which I mentioned.
WHY I NEED THIS ?
As you can see in Image 1, If I apply these colors to Table 1 only, then the user will not be able to see the value V or SL or any values belong to which date ?
Note - I am doing this for the whole year. But if someone can send me VBA code then I can do it for the rest.
View 9 Replies
View Related
Sep 11, 2009
I need to create conditional formatting on a worksheet where I need to change the cell colour based on 6 cases.
The VBA that I have found does not work with referenced cells to another worksheet in the workbook which I need as all the data is referenced with calculations.
I also need to specify the cells that the VBA will apply to as I need to apply 6 or 7 different different sets of conditional formatting on the same worksheet to different groups of cells.
View 14 Replies
View Related
Aug 28, 2009
I have a bunch of images which I want to be able to display in different cells depending on certain values or conditions. Say for example you have a drop down box with a list of fruits. When you pick a fruit, it displays its picture. Is this possible? If all of the pictures exist somewhere on the sheet, can you reference the pictures to have them appear in a cell rather than having images always "floating" over the sheet?
View 3 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
Oct 18, 2008
when the largest number in column B the hotel in column A should be in bold.
So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.
You can see here on the image:
additionalimage.gif
View 4 Replies
View Related
Aug 2, 2013
I have an inventory log that requires multiple cells in different columns to be unlocked based on a reference cell's input.
So, if a cell in column E has "MORNING" entered then cells L/M/N are unlocked and said user can input data for that row, and only that row. If anything else is in E, then L/M/N are left locked.
Is there a way to do this without coding, just using regular IF() in the cell directly; IF(ISTEXT(E3)=MORNING, Unlock, KeepLock)? I know that's nowhere close to being a legitimate statement, but it's the best way I can translate my thoughts.
View 9 Replies
View Related
Feb 1, 2014
I have a column containing 8760 item (365*24), I divided them into days, one column for each day, modifying a macro I found here. Now I want to conditionally format these columns in themselves with color scales. But if I select 365 columns at the same time and do it, it takes the highest and lowest of all the numbers and format. I want it to format each column using the highest and lowest values in itself. How do I do this without selecting every column one by one and turning conditional formatting on manually?
View 9 Replies
View Related
Mar 26, 2008
I've copied a sample of my worksheet below (there are 52 of these in my workbook). i'd like excel to place conditional formatting in column M (i.e. "Promo Proice Per Unit"). So that if the price is different to the price in column L (i.e. "Shelf Price Per Unit") then excel will apply conditional formatting to the corresponding cell in column M.
So for example, in row 10 (product 5) the promo price (column M) is different to the shelf price (column L). It'd be great if excel wold then automatically format that cell as the example in the below table (i did the formatting manually).
When i tried to do this using the format/conditional formatting way i couldnt figure out how to tell excel what to do as its a little more advanced than the options that it gives you in that window?
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH1=HIJKLMN1Week commencing - 04-Aug-08 ??? 2 Weekly Baseline Sales Log BaseRetail Price 3Account A 4 Sales MTSales UnitsSales - Shelf Price per UnitPromo Price per Unit% Discount5 6Product1 789.5 3,158.0 6.671$4.19$4.190.07Product2 1,529.0 1,529.0 7.332$4.19$4.190.08Product3 1,373.0 5,492.0 7.225$0.00$0.000.09Product4 8.2 16.4 2.105$0.00$0.000.010Product5 1,636.5 6,546.0 7.400$4.19$3.490.011Product6 1,363.0 5,452.0 7.217$4.29$4.290.012Product7 2,829.0 5,658.0 7.948$6.99..........................................
View 9 Replies
View Related
Jan 29, 2010
I want a to include conditional formatting for Column D in my spreadsheet to highlight cells that have information in them but that are less than 17 digits. (The cell will be filled with alpha & numeric data)
How do I do this?
View 9 Replies
View Related
Dec 21, 2013
I'm trying to build a simple spreadsheet that allows me to auto highlight the day in the week and part of the column.
I include a sample sheet... above saturday there is the formula I use to determine the day of the week.
View 14 Replies
View Related
Oct 13, 2009
If a cell in Column F = Closed Then, the entire row is filled green. If a cell in Column F = Open Then, the entire row is red with a strike through. I'm guessing this is simple, but I can only conditionally format on a cell-by-cell basis right now
View 2 Replies
View Related
Feb 17, 2014
I have a value in column H7 as 2000.
I have columns M to X labeled Jan to Dec. I want to highlight in yellow values in the row M7 to X7 if they are greater than 2000!
Then, I need to copy this conditional formatting down the columns under M to X, but under column H the values will be different, but I still want the same results, values greater than the value under column H then highlight in yellow.
I thought I had the answer but when I tried to copy down, it was making cells yellow that were clearly less than the column H value.
View 5 Replies
View Related
Nov 5, 2013
I have a query relating to conditional highlighting - specifically highlighting a cell's column and row based upon whether it contains data or not i.e. if it is blank, it's column and row remain unhighlighted, but when it contains data, it's column and row should become highlighted. I could probably manage this myself but I have a few more specific requirements which I have illustrated with a few screenshots, however I can't see how to make these available to view here!I
View 1 Replies
View Related
Mar 25, 2014
I created added conditional formatting to the Response Due column, to keep track of when response was due.
I only want the conditional formatting to be applied if the cell next to it is blank.
For example, the last one on my attachment is due 3/26/14 but I have already submitted a response (see column K)
What can I add so that only those dates next to a blank in column K get the conditional formatting?
Sample.jpg
View 2 Replies
View Related
Jan 6, 2010
I have a spreadsheet where column H can be either yes or no. When column H changes to Yes I want the background of the whole row to change to Red.
e.g. Cell H4 contains the word no. I then change this so that it says "yes". I want the background of row 4 to go red. so A4, b4, c4, d4, e4, h4 all go red.
With the type of conditional formatting i normally do i can only get cell H4 to go Red by putting cell value = yes as the condition. I cant for the life of me think of a formula or way round of doing this.
View 4 Replies
View Related
May 18, 2013
I want to have Excel highlight one or two numbers/cells in one column based on the value in another cell. I am attempting to do this using Conditional Formatting.
For example, if X1 is BETWEEN A2 and A3, I want to highlight A2 AND A3. I have used various AND and IF functions to achieve this goal.
Also, if X1 is EQUAL to A2, I want A2 to be highlighted. This is easy to do with a simple formula.
But my problem is this: if X1 is equal to A2, I want ONLY A2 highlighted. If X1 is between A2 and A3, I want BOTH A2 and A3 highlighted. I cannot figure out how to achieve both. For example, when X1 = A3, A2 AND A3 are being highlighted, instead of just A3. I would prefer to do this without a macro, if possible. Can this be done?
Example formulas I have tried which easily achieve the highlighting of two cells when the value is between them:
Condition 1=IF(AND($I$35>I8,$I$35<I7),1," ")+IF($I$35<>I7,1," ")
Condition 2=IF(AND($I$35<I6,$I$35>I8),1," ")+IF($I$35<>I7,1," ")
It appears that the +IF, etc. functions are not doing anything.
This is more complicated by the fact that the value of, in the above example, I35 is based on a value from another sheet (I used a function to transfer a value from one sheet to I35 in the other sheet). As data are entered on Sheet1, the value in I35 in Sheet2 changes.
View 14 Replies
View Related
Sep 29, 2011
I am putting together a worksheet with all my teams tasks. I have columns A:U and Status is column "M". What i want to do is when ever i select Complete from the list in Column "M" i want to the row to turn Green.
I tried the following:
Selected the Range
From Conditional Formatting menu selected Manage rules
Selected new rule then Use formula to determine which cells to format
formula i used =$M$2=complete and selected green from the format menu
BUT nothing is happening. What am I doing wrong.
BTW the Values in Column are Complete,Development, Not Started Research, QA/UAT. If I can conditionally format based on these selections that would be even better.
View 3 Replies
View Related
Mar 7, 2012
I have a column of cells containing dates. I need to change the colour of any cell which is 7 days older than todays date. I would like to leave the header and any blank cells unchanged. Every formula I've tried has changed the enire column, or if I selected a range, the entire range.
View 5 Replies
View Related
Oct 11, 2013
I am totally new to Macros. I need a Macro which should format a column based on the value of another column.
Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?
View 9 Replies
View Related
Feb 25, 2009
I'm trying to highlight cells a certain color based on the value of the cell 1 column to the left. How would I be able to do this in conditional formatting?
Example)
Highlight the cells in "Visit 2" column if it is greater than the "Projected Visit 2" column.
NameProjected Visit 2Visit 2Person 124-Nov-0820-Nov-08Person 226-Jan-0927-Jan-09Person 32-Jan-082-Jan-08Person 430-Dec-0829-Dec-08Person 46-Nov-0830-Oct-08
View 9 Replies
View Related
Dec 2, 2013
I'm having to successfully add three conditional formatting rules, and associated cell interior colour formats if any of the rules are met.
Each time I run my macro, I want to refresh every cell, within a defined range within a Col P, with these rules.
So far, I can add the three rules to each cell, but when I try and add the format colour - for when a rule is met - I keep getting a 'Subscript out of range error'.
(As an aside, is my For / Next loop approach the best one to take to add the formatting to each cell, or can it be written much simpler?)
Here's my script:
VB:
Dim Col_P As Range
Dim Cell_in_ColP As Long
'Set up range of cells to add conditional formatting rules to
Set Col_P = Worksheets("tRIIO Pack Notices").Cells(2, 16).Resize(Lrow - 1)
[Code] ......
View 4 Replies
View Related
May 24, 2014
conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:
1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?
I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:
1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.
View 5 Replies
View Related
Jul 2, 2014
Cells in Column F change colour under certain conditions. How do I cancel or apply no format to cells in Column F if any date is entered in Column G.
View 3 Replies
View Related
Mar 26, 2009
I want to format 3 columns in excel depending on certain value from another column.
By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.
I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.
View 2 Replies
View Related
Sep 16, 2009
I have a spreadsheet showing names, quality percentages and times taken to answer a call in 3 columns. I would like to highlight the cells that fall within the top 20% of those shown (e.g. if there are 100 quality percentages I want to highlight the top 20 not those over 80%) also the same with call length.
I have a column of numbers and times (mins and secs) and what would like to do is set a conditional format so that the cell turns a colour when the number of any cell is within the highest 20% and the other column turns a different colour when the time is within the lowest 20%.
View 5 Replies
View Related
Jan 30, 2010
For my example, in column B I have a list of guest names. These names will repeat based on their entries.
In column C I have amounts next to their name.
If the total of all amounts next to their name equal $1,000 or more I want all the rows that their name is in to be highlighted.
I attached an example.
View 3 Replies
View Related
