Prevent Resetting Of Conditional Formatting Rules?

May 24, 2013

I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.

That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?

View 5 Replies


ADVERTISEMENT

Display Conditional Formatting Rules?

Oct 9, 2013

how to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.

View 3 Replies View Related

Combine 2 Conditional Formatting Rules?

Apr 12, 2014

I have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true

(1) =$B3:$B6="y"
(2) =AND(COUNTIF(K$3:K$6,MIN(K$3:K$6))=1,K3=MIN(K$3:K$6))

View 5 Replies View Related

3 Color Conditional Formatting Formulas Rules?

Jun 23, 2012

How do I create 3 color conditional formatting formulas for the following rules?:

if today is 15 days or greater before the day in the cell then green

if today is between 14 and 7 days before the day in the cell then yellow

if today is within 6 days to zero days before the day in the cell then results

anything is clear

x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012

View 9 Replies View Related

Script To Add Conditional Formatting Rules To Column Of Cells

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

Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells

Feb 5, 2013

how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

View 1 Replies View Related

Prevent Conditional Formatting From Moving With Cut Cells?

Jan 27, 2011

I am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.

Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.

Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.

The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?

View 7 Replies View Related

Maximum Number Of Conditional Format Rules

Jan 14, 2010

I need to add more conditional formatting to my spreadsheet. But it seems to belimiting me to 3. Is there a way of increasing it?

View 9 Replies View Related

Automatic Formatting Of Cells Based On Rules / Criteria

Jul 22, 2014

I have a long set of data that I extract a "table" out of based on index matching controlled by 4 different criteria. This part is working as intended. However I need to format the cells with the corresponding "fetched data" based on another criteria. Makes sense? Have a look at a spreadsheet i made representing my problem. It has comments for the problems.

excel tableformatting problem.xlsx‎

View 1 Replies View Related

Formatting Group Of Cells To Have Different Decimal Rules Based On Decimal Value

Aug 3, 2006

Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.

View 9 Replies View Related

Prevent #DIV/0 In Conditional Average

Feb 16, 2008

I have an average array formula that works fine when it has something to average. However I get the #Div0 error when there is nothing the average. I need to replace the error message in the cell to be blank. Until there is data to be averaged. I have tried to use ISERROR but struggling to get it to work with my multiple criteria.

This is the formula i am trying to amend:

=AVERAGE(IF('By All Students'!$S$3:$S$100>=80, 'By All Students'!$S$3:$S$100<92, IF('By All Students'!$Q$3:$Q$100="11.1/AR",'By All Students'!$O$3:$O$100)))

View 5 Replies View Related

Prevent From Formatting Text File?

Jul 23, 2009

When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?

View 4 Replies View Related

Stop/Prevent Web Query Auto Formatting

Apr 23, 2009

I use a query table to import data into a workbook which works fine

With wsActiveSheet.QueryTables.Add(Connection:=sQueryString, _
Destination:=wsActiveSheet. Range("A1"))
.Name = CStr("main.html?LANG=de&search=true&searchterm=" & sIdentifier & "_1")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows 'xlInsertDeleteCells
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = "9,14"
.WebPreFormattedTextToColumns = True
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before

View 9 Replies View Related

Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?

Sep 16, 2013

A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.

dust 1.xlsx

View 2 Replies View Related

Conditional Formatting Based On Another Cell Conditional Format

Mar 20, 2013

Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:

If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.

Possible???

View 3 Replies View Related

IF Statement Using Formatting Criteria (NOT Conditional Formatting)

May 5, 2009

Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:

if(A1=blue background,"Yes","No")

or

if(A1=red text,"Yes","No")

etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.

View 5 Replies View Related

VBA To Convert Conditional Formatting To Fixed Formatting?

Jul 5, 2013

Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...

View 9 Replies View Related

Resetting Toolbars

May 29, 2009

my excel is no longer showing any tool bars and I can not get them back, is there any short cuts to bring back the menus? I can’t even do a right mouse click on the sells for some reason. The only tool bar that is showing is the formula bar.

View 10 Replies View Related

Resetting AutoFilter

Aug 13, 2009

Code runs rather slow. Is there a better way to achieve the same results?

View 5 Replies View Related

Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View 2 Replies View Related

Resetting Drop Menu's

Nov 1, 2009

If I change the country in cell B2, I would like to have that the cells D2, E2 and F2 show the same name as cell R2. (This is always the first option of the drop menu's)

Cells D2, E2 and F2 are made by using the validate option in order to get drop down menu's.

The source information is in the hidden columns I-Z.

So for example:
if I change cell B2 to Belgium, I would like to see that D2, E2 and F2 also show Belgium immediately. I can then select the place I want to move from Belgium to by using the drop menu in D2 (for exampla Belarus).

As I selected for D2 Belarus, this has to be shown in E2 and F2 also.
I can now select E2 (for example Austria).

If Austria is selected in E2, I want to see this also in F2.

To recapitulate:
If I select Belgium as starting place, I want to see in movement 1,2 and 3 also Belgium, as there hasn't been anymovement yet.

If I let my army go from Belgium to Belarus, I want to see for the two remaining movements Belarus (as this means that the army remains in Belarus)
If I let my army go forward after Belarus to Austria, I want to see in the last movement phase Austria (no movement). If I still want my army to move I can still adapt it.

View 2 Replies View Related

Resetting Shape Locations

Apr 12, 2012

I have many spreadsheets that use shapes such as arrows and other symbols. The process for these spreadsheets is to move the shape (arrow for Ex) to indicate a specific location of pain or something like that.

Once the arrow is in position, the spreadsheet is printed to PDF and and a snapshot (in adobe) is "taken" and the picture (with the arrow) is pasted into our application.

My questions is how can I move the shapes back into position when you want to do it all over again.

When I have a few shapes I use

PHP Code:

Set sShape = ActiveSheet.Shapes("Right Arrow 6") 2
    With sShape
       .Top = 220
       .Left = 91
       .Rotation = 20
   End With 

For each shape

But if I have lots of shapes, 50+, it's a bit more cumbersome.

Can I loop through the shapes when I enable the macros to pick up the current positions and use that information when I do a reset (loop back again to move any shape that were touched back to where it was when the spreadsheet was opened?

Currently I have them close the spreadsheet without saving and just reopen.

View 3 Replies View Related

Resetting Option Buttons

Mar 25, 2005

I have a spreadsheet to keep track of purchase orders, and I use groups of option buttons to run macros to filter the info by category using auto filters. Not sure if I'm explaining this well, but as an example, I have one set of option buttons for whether the items are late, and one for whether I have flagged the order for some reason, and a couple of others. It does everything I want, basically.

I have a button which resets all the autofilters in one go, but the problem is it doesn't put the option buttons back to the "Show all" option for their categories. I tried recording a macro and clicking in the "Show all" option so I could look in the code to see how it refers to it, but it just says to run the macro, nothing about selecting "Show all".

Also, the way I reset all the autofilters is to have a macro that goes to every autofilter in turn and selects "(All)". Is there a better way of doing this?

View 9 Replies View Related

Resetting All Radio Buttons Within Worksheet

Feb 25, 2013

I am creating scorecards for our company and ratings are inputted using radio buttons (form control). I have a "clear" macro that clears all fields and radio button values (none is ticket). This macro is called throughout different sheets, as different roles in the company have different KPIs and should be on different sheets. I need a code that detects all radio buttons within a certain sheet and clear their values. I cannot put specific radio button names, as I have a lot.

View 2 Replies View Related

Resetting A Currency Field Column

Feb 26, 2008

I have a column (L8 down) of cells formatted as $'s

At the end of the column is a sum formula.

At the end of each month the client wants to 'reset' all the $ amounts back to $0.00

Is there a macro that would reset the column of $'s back to $0.00 without removing the formulas like ClearContents does?

View 9 Replies View Related

Excel 2010 :: Resetting Height Of Formula Bar?

Dec 19, 2011

When I open Excel 2010, the formula bar appears about 6 times as tall as normal. I have not been able to find any way to reset it to normal size.

View 11 Replies View Related

Code Not Resetting Cell Color Back?

Jun 13, 2014

VBA code in worksheet won't revert a changed cells color back to 0(white) after removing the comment.

Observations: code does set the desired cell colors to Yellow after inserting a comment, however removing said comment, and re-activating the worksheet does NOT reset the color after deactivating/re-activating the worksheet.

[Code] ......

View 2 Replies View Related

Resetting Workbook Controls To Set Postions/sizes

Jun 9, 2009

Is there a way for me to have combo boxes, labels, and command buttons to be moved to a set position rather then just moving by intervals? What I need this for is every time one of my worksheets opens it refreshes the data and does this via lookup formulas and a macro that searches for values and deletes the rows with the matching values, it also adds rows to the top after it is done deleting. This causes all my controls to move randomly and I need some code to reset them to their original sizes and positions after the code that adds and deletes rows is done running.

View 2 Replies View Related

Edit Range Names Without Resetting AllowEditRanges

Apr 1, 2008

Every time I re-define a named range, either through the UI or via macro, the permissions I had set for that range are seeminly deleted from the allowEditRanges collection. (When I go to view them under tools>protection>allow users to edit ranges>users allowed to make changes without password ends up blank)

I would like to re-define the range in a VB macro without this undesired effect. Alternatively, a script that reads the permissions for that range before my code and writes those same permissions after would do fine.

View 5 Replies View Related

Macro Resetting Pivot Table Field To Count

Feb 15, 2007

I've recorded a macro that clears a worksheet, fetches data from an Access Table and then creates a new pivot table. The Pivot Table Fields are summarized by "sum" and this worked the first few cycles for the macro. Now it is returning the Pivot Table Fields summarized by "count". Can an option be inserted into the existing code to specify "sum"? Here is the

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'raw data'!R1C1:R205C12").CreatePivotTable TableDestination:="", TableName _
:="PivotTable14"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet. Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable14").SmallGrid = False
ActiveSheet.PivotTables("PivotTable14").AddFields RowFields:=Array("Name", _
"FIELD_ASM_USER_NAME", "Data")
With ActiveSheet.PivotTables("PivotTable14").PivotFields( _
"SumOfSumOfSumOfCYYTD_SHARE_QTY")
.Orientation = xlDataField
.Position = 1
End With..................................

View 2 Replies View Related







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