Retaining Conditional Formatting On New Rows?

Jul 16, 2013

I have a spreadsheet for recording property maintenance. All properties are grouped by a city zone in a specific colour, and there is conditional formatting on this. Also drop down lists for properties, contractors etc.

This all worked well, until I needed to start inserting rows. All the conditional formatting and lists didn't replicate on the newly inserted rows.

Also, we need to assign a unique reference number to each row but currently manually inputting them - very inefficient way of doing this as they will inevitably get mixed up and confused as rows are removed.

Spreadsheet.jpg

View 9 Replies


ADVERTISEMENT

Retaining The Formatting Of A Cell?

Apr 24, 2014

I am creating a worksheet that uses a series of if formulas to determine the correct cell to use in a table outside the printing/viewing area. Something along the lines of "if X, Y, and z happens then use what is in cell AY34". I have the formula working, so I can get it to go pick up what is in the cell I want.

The problem I am having is that there is a heading to the text in cell AY34 that is bold, but the rest of the text in the cell is not bold. Is there any way to drag the formatting along with the data and not just the values?

View 3 Replies View Related

Retaining Formatting And Special Characters

Sep 9, 2007

I have created a multiple choice quiz maker that randomizes the questions and responses. This means that the quiz questions/responses must be copied and pasted each time a new quiz is generated. But in doing so, Excel loses formatting (such as super and sub scripting) and some special characters (like pi, alpha, the degree symbol).

Is there a way to get such things to copy properly from cell to cell, sheet to sheet, using Excel?

View 9 Replies View Related

Pasting Text Without Retaining The Formatting

Nov 20, 2009

I am in a situation where I have to copy-n-paste text from a webpage into Excel. When I do this, the pasted text keeps the formatting of the webpage. The font is the webpage font. The background color is the background color of the webpage. Etc.

I know that I can paste just the text itself, without any of the formatting, by right-clicking, selecting "Paste Special...", and then selecting "Text" instead of "HTML". I am wondering if there is an option somewhere that will do this automatically when I hit CTRL-V instead of requiring me to pull up the Paste Special dialog box.

View 9 Replies View Related

Copy Table Retaining All Formatting And Cell Sizes

Aug 27, 2013

I have a workbook that has multiple tabs with data already entered. I have also built a series of tables/forumlas to display summaries of the data. My desire is to be able to copy this table with all formulas/formatting/cell sizes to the various existing tabs.

View 3 Replies View Related

Conditional Formatting For Two Rows

Feb 15, 2012

I have a spreadsheet with 10 columns and 8000 rows. column 2 contains names and 3 contains districts of residence.

I need to highlight (or delete) whole rows of duplicate entries in the name column(2) that are in the same district (column 3).

Traditional conditional formatting highlights all duplicates. I just need those duplicates in column 2 that have the same text in column 3.

View 2 Replies View Related

Getting Conditional Formatting Rows?

Jun 7, 2012

I have rows of information linked to other sheets with forms to fill out (master sheet). I want to be able to have a red fill if one of the cells is blank, which I do have working now, but I want a clear row if the "indicator cell" is blank.

View 1 Replies View Related

Conditional Formatting For Multiple Rows

Jul 8, 2009

I have a spreadsheet of a couple hundred products, their sales over the last 18 months, and then each product's 18 month average monthly sales. What I would like to do is format them so if any of the months' sales are below their respective average, that cell/month with be highlighted. Excel told me that I cannot do this for multiple rows at a time, and I don't want to go row by row for this many products. Attached is a sample file of the first row.

View 4 Replies View Related

Copy Conditional Formatting To Other Rows

Jan 15, 2010

I'm running a spreadsheet at the moment in 2007, and one of the funcitons I've made it for certain cells to either change format if the number is to big or to small depending on the cell it is.

I have formatted all for one line against duplicate scores on the same line.
The problem is this is time consuming and there are 22 different lines.
Because with Conditional Formating you can't simply drag like you can with formulas, I was wondering if there was a more simple approach. I have demonstrated the formating required on Row 6, and require the same formating on rows 8:10, 14:26, 32:36.

Also, just to make things complicated, the values change for each line.
I have attatched the file. Row 6 with the label DEMO LINE contains all the correct formating I require.

View 3 Replies View Related

Insert Rows Through Conditional Formatting

May 15, 2013

Doc1.docx

I need the Left picture to look like the Right picture... There's over 30,000 lines of data.

View 3 Replies View Related

Conditional Formatting On Different Rows With Different Query

Aug 20, 2013

I have the following data:

C D E F G
Jan Feb March Q1 TargetThreshhold
£ 2,067 £ 1,978 £ 2,161 £ 2,100 £ 2,310

£ 1,492 £ 1,422 £ 1,611 £ 1,600 £ 1,760
£ 2,718 £ 2,683 £ 3,263 £ 2,400 £ 2,640
£ 2,853 £ 2,335 £ 2,481 £ 2,300 £ 2,530
£ 1,412 £ 1,533 £ 1,526 £ 1,600 £ 1,760
£ 1,067 £ 1,133 £ 1,246 £ 1,400 £ 1,540

And I am wanting to use conditional formatting. But wondering if there is an easier way than what I have at the moment.

The Formatting im using is:

=$C4>=$G4 Where value is TRUE the cell returns red and this applies at the moment to the First Row.

Is there a way I can get Conditional formatting to recognise the next cells without typing for each set of rows.

aka Row B would Read =$D4>=$G4
Row C would read =$E4>=$G4

etc?

Id rather not have to do a conditional format for each row as I have about 50 rows of data to do this to

View 4 Replies View Related

Conditional Formatting For All Rows In Each Sheet

Dec 4, 2008

I want this macro to apply the conditional formatting to all rows in each sheet that have "BE" in col G. The macro works fine in Cell A2 only. And only on the worksheet that I have active at the time that I run the macro. Can anyone tell me how to fix this?

View 4 Replies View Related

Using Conditional Formatting To Delete Rows

Jul 26, 2007

I have a worksheet that I use Conditional Formatting to check the dates in a total of 5 columns. In those columns, if the date is before 8/1/2006, it changes the cell color to red. Is there a formula I can put into Condition 2 to that would then clear out the whole row if within those 5 columns there isn't a red cell? This would take hours of work off of my slab.

******** ******************** ************************************************************************>Microsoft Excel - Credetnialing Master.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAT1=
ATAUAVAWAXAYAZBA1LicenseExpirationInsuranceExpirationDEA_ExpirationStateStateDrugNumberStateDrugExpirationAttestationDateNPDB_Date212/31/20078/2/20071/31/2009*229312/31/20073/7/20073/26/2007312/31/20075/1/20082/28/2010*253812/31/20077/6/20067/24/2006412/31/20078/26/200712/31/2007*256312/31/200712/12/200612/14/2006512/31/200710/19/200712/31/2009*276812/31/200711/28/200412/8/2004612/31/20079/25/20078/31/2007*303012/31/20074/6/20064/17/2006712/31/200712/3/20077/31/2009*315212/31/20075/3/20055/11/2005812/31/20078/2/20078/31/2008*323612/31/20072/28/20073/26/20079**10/31/2009****7/7/20031012/31/20078/10/20073/31/2008*353512/31/20074/11/20064/24/20061112/31/20072/1/20083/31/2009*365012/31/20
.........................

View 9 Replies View Related

Conditional Formatting (>) - Highlight Rows

Jan 12, 2010

I have data from Col A to Col M, in Col C i have amounts, i need a conditional format that will highlight rows from Col A to M in yellow which are >5,000,000.00 and

View 9 Replies View Related

Need Conditional Formatting For 2 Columns, Unlimited Rows.

Mar 10, 2009

Im having a little trouble with this, Im dealing with a list, and I need to change the colour of rows, based upon a value in 2 different columns.

Im close, but cant quite get it.

Basically its like this, the data in these cells will only ever be either; blank in one or the other, both are never blank. or a number up to about 3 or 4, usually 1 or 2.

View 2 Replies View Related

Conditional Formatting Multiple Rows Independently

Apr 22, 2009

I have an Excel worksheet with about 300 rows and 8 columns per row. I want to shade the highest number and the lowest number for each row. I assume the built in conditional formatting is the best place to start, but I don't really know. Sample worksheet attached.

View 2 Replies View Related

Conditional Formatting To Change Colour Of Rows

Aug 13, 2009

I want to use conditional formatting to change the colour of the entire row when the date on A1 is any date in September (01/09/09 to 30/09/09).

View 10 Replies View Related

Conditional Formatting Rows In Pivot Table

Mar 25, 2014

I have a pivot table which shows below... I need to set up a conditional format to highlight the row where all fields are "0"

Company
Deals
Opps
Days since Deal
Days since Opp
Days since Meet

Co 1
1
7
80
20
20

Co 2
0
0
0
0
0

Co 3
1
4
30
30
28

Co 4
3
3
30
30
37

View 1 Replies View Related

Deleting Rows Based On Conditional Formatting

Jun 11, 2009

I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.

Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.

Here's what I already have:

View 10 Replies View Related

Conditional Formatting For Multiple Columns And Rows?

Mar 28, 2014

I have data in cells A2 - N2 and cell O2 is currently blank. I am trying to set conditional formatting where if O2 has a value entered into it, it will conditional format A2 - N2 and highlight the entire row red.

I tried a formula like =O2"" but that does not work. Also tried a few other things but no luck.

Also, once the formula is set, what is the best way to apply the conditional formatting to rows 3 - 30. I tried the format painter tool but that seems to want to extend the formatting range from rows 3-30 and not just per row.

View 3 Replies View Related

Trigger For Conditional Formatting To Hide Rows

Oct 21, 2006

I found in this forum some vba code to insert into an excel spreadsheet a routine to hide rows if a value is < 1 in a column (Column B for this example). My problem is that it executes as you go down each cell in column B. I want to press a function key at the end of working on the spreadsheet to have it go line by line to hide rows that meet the condition. The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Application. ScreenUpdating = False
' Dim R As Range
' Set R = Application.Intersect (Target, Range("B7:B7"))
' If R Is Nothing Then Exit Sub
With ActiveSheet
For Each Cell In Range("B5:B6")
If Cell.Value < 1 Then
Cell.EntireRow.Hidden = True
End If
Next
End With
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Sort Rows Housing Conditional Formatting

Nov 1, 2006

I have a spreadsheet in which I needed to add the row totals of cells affected by conditional formatting. Ozgrid came to the rescue here (kudos to Dave H and JimFuller1), but now if I sort by these totals the results do not match the rows they are associated with. The attached spreadsheet shows the values the users want to see on Sheet1. The user changes the highlighted values in the rows by changing the values in A1 and B1. These index a set of tables on Sheet3, and Sheet2 mimics the CF conditions in order to provide the totals in col V of Sheet1.

Now if I filter a Sheet1 field on nonblanks (to get rid of the blank rows) and then attempt 'Sort Descending' on Col V, all the rows appear to sort correctly with the exception of Col V which now appears to give the wrong total for the row.

View 4 Replies View Related

Conditional Formatting To Format Cells On Several Rows Below According To The Day In 2007

Sep 8, 2009

I´m having aproblem with Excel 2007 about Conditional Formatting. I have a row of Dates for example 02-01-2009 03-01-2009 04-01-2009 , etc in different columns.

Then what I want to do is use Conditional Formatting to Format cells on several rows below according to the day (if its weekend paint red, if not, dont do anything). I'm using the "Use a formula to determine which cells to format" and the condition is (supposing the cells with dates are A1 to C1)
=WEEKDAY(A1:C1) > 5 .

So with those 3 dates provided lets suppose Januar2nd is not a weekday, so the outputIwant is: Red White White, (Next Row) Red White White, etc for several rows.

Now what I think is not right is it only works for the row in which I have the cursor so its like: (Lets imagine I selected 3 rows on which I want to see that output) Red White White (The row in which the cursor is works fine), but the next row goes Red Red Red and the third the same.

View 5 Replies View Related

Conditional Formatting Similar To Shading Alternating Rows

Aug 18, 2006

I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):

Formula is: =isodd($A2)

The error I keep getting is this:

"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."

I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.

I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.

View 10 Replies View Related

Conditional Formatting Formula Based On Rows Equaling Each Other

Jan 15, 2014

I have two columns

Column L and Column W

When I input something into Column W, I want it to turn green if it is equal to the corresponding value in Column L

So say we're on Row 50

And I put $280 in Cell W50

I want it to turn green if W50=L50

And red if W50 does not equal L 50

Infinitely in the same column

View 5 Replies View Related

Macro For Deleting Duplicates / Comparing Rows And Retaining Minimum Value?

Apr 6, 2014

I have data from Columns A to D. I want to do the
following:

1. Look for duplicates in all column A

2. For each duplicate found in column A, check if all values in column B are also duplicate.

3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.

For example:
A B C D

Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....

View 13 Replies View Related

Colour Rows Based On List Colours (No Conditional Formatting)

Oct 3, 2012

As far as I can tell Conditional Formatting will not work for this problem.

What I need now is some code to colour the rows in the data sheet ("Standards_Data"), so some other code (which is working fine) can then colour data points on a multiple graphs (which will be on the "Graphs" sheet). I've played around with as much code as I can find but nothing has the flexibility that I need or doesn't seem to work at all.

The idea is that users can adjust the colours using the Column A on the "Graphs" sheet to best highlight certain things. Also this list could then be edited as old Standard ID's become redundant and new ones come on line.

So in a nutshell: Colour rows in "Standard_Data" based on value of Standard ID (Column 4) according to the corresponding colour found in the list in the "Graphs" sheet.

I've attached a trimmed down version of the workbook.

View 4 Replies View Related

Conditional Formatting Entire Rows Based On Specific Columns?

Mar 25, 2014

I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:

Month
2010
2011
2012


[Code]....

The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.

View 3 Replies View Related

Conditional Formatting - Duplicate Rows Based On Multiple Columns?

Apr 8, 2014

I'm looking for a Conditional Formatting formula that will check two columns before highlighting the duplicate rows. I need it to be conditional formatting because I know nothing about writing macros or vba (what-ever that is?). Data is entered into Columns A, B, and C. I need to check both column A and C before it highlights the duplicates, based on those two columns. (The format only unique or duplicate values checks only one column.) I have attached an example, but this is just an example, as I have hundreds of lines to go through on the original. (For this example, Row 2 and Row 7 are the duplicates I need highlighted.)

View 3 Replies View Related

Hide & Show Rows Based On Multiple Conditional Formatting

Aug 6, 2009

I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:

[url]

The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved