Save Conditional Format Colour But Delete Condition Format
Dec 19, 2006
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
Below I have an example from A1 to V1 which have 3 conditional formatting conditional format 1. if value is 0 then colour red and font white conditional format 2. if value is from 1 to 10 then colour blue and font white conditional format 3. if values 11 to 25 colour green, font black
i m trying to format a cell so that it colours in red when a specific text is chosen in another cell, ive tried conditional formatting but I cannot get it to work. e.g column A has a drop down list, if GB or IEN is chosen in that cell then cell in column K will become red. (as if these two are chosen then they have to input a number into column K). Once they have inputted the number into column K i want it to go back to its original format. Or if they dont select GB or IEN then it would stay in its original format aswell.
I would like to apply different conditional formatting at different times with a click of a button. I setup a dummy and turned on the recorder and recorded this
Range("A7:N7").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C7=1" Selection.FormatConditions(1).Interior.ColorIndex = 37 Range("A1").Select End Sub
I tried changing to this
'/Conditional Format - OTHER EXPENSE B/L Set rngConditional = wsData.UsedRange With rngConditional...............
I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?
Im having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
I have five fields that have a conditional format applied to them: (see attached). Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
A fairly simple one I imagine but I've never come across it before, so I ain't sure what to use!
I am working on yearly budgets/balances, and where the total spend against a budget has gone over the budgeted amount I want my formula to format the text red.
Example Budget Spend Variance 30 35 -5
The formula is also doing a sum of budget minus spend, so if the result is below zero the text needs to be red basically.
I have been working on a spreadsheet but I use autoformat for.
For example if the date in the cell matches today's date then it turns the text read and makes it bold.
I was wondering if there was a way to format several cells in a row if a certain criteria was met.
for example
A B C 1 Item Cost Quantity 2 Car £11,520 2 3 Bike £7,500 1 4 Tyres £50 4
Now for example if I set a condition whereas the quatity is greater than 3 then it would highlight the whole row.
So in the example above I would like to to highlight row 4 and Fill colour cells A4,B4 & C4 with a yellow background and Red Text and make the texted bold.
What would be the quickest and most efficient way of doing the following:
Example
Sheet 1: clicking once, twice, three times in the following cells to change the fill to Red, Yellow, Green respectively (in Row 22, COLUMNS E H K N Q T W Z AD AG) and mirrorring this format/fill on:
Sheet 2: , mirror the selected fill on sheet1 in E22, H22 , K22 in in E3:N3 respectively example E22 (sheet1) is mirrored now in E3 (sheet 2); H22 (sheet 1) is mirrored in F3 (sheet2) etc etc.
I have 35 tabs formatted in the same way - each tab, as with Sheet 1 is linked to a different row on sheet 2 in the same range as is sheet 1 and would need to replicate the mirorring on Sheet 1 to each of the other 34 tabs.
I set format condition in range by tool bar(=if formula is yes then display cell.interior.colorindex=3), but I have no idea how to find out (.address) the red colour cells are displayed in range with VBA?
i have set some cells with format condition, so when the value of it is true the entire cell should be gray "font,border,pattern". so the data will be invisible. so it dose that but when i print the font is back to black? which should be gray? i dont know why its doing that.
i tried this in other computers some of them work ok and most of them dont?? maybe its the printers? if so whats the solution?
I'm trying to Multi-Select rows in order to format all rows, in "One Step" if the cell in col. "A" = 1. I managed to write the hereunder code but I'm still frastrating because I don't like the first RNG setting command. How can I avoid declaring RNG as row(1) and still be able to run the code. I assume I need some other Range set for RNG which, unfortunately, I could'nt find and the command I used includes row1 in RNG although cell A1 = 5.
Sub Conditional_Multi_Rows_Select() Set RNG = Rows(1) ' this is my problem LR = [A65536].End(xlUp).Row For R = 1 To LR If Cells(R, 1) = 1 Then Cells(R, 1).EntireRow.Select Set RNG = Union(RNG, Selection) End If Next RNG.Select Selection.Font.Bold = True Selection.Font.ColorIndex = 3 End Sub
I had in mind to use:
Set RNG = Rows(65536) but this will not make it a "neat" vba code, either.
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.
I have a listbox that lists some items from a spreadsheet, but what I cannot figure out is how to have the font color of the items be RED if the value if negative, and default BLACK when positive...
I am using pivot table for my customer aging which a sample is enclose. What I want to do is to Highlight the field " Customer Name " in colour if the the Field " Type of guarantee" is other than 0. Also a message of " Credit limit Exceeded" if the outstanding is more than the Credit limit.
I am trying to save a file into a CSV format. When I choose that save as option the file is saved with the commas but a need each field to be surrounded by quotes.
Is there an easy way to do this? I know I could append the " around the data in each cell with a formula, but I'm hoping there is a cleaner way to do it.
I would also like to set the extention of the saved file to .txt when I save it, but Excel 2000 does not seem to let me do that.
The starting sheet has lot of text cells with empty columns and rows between them, without pattern. I need to do the following:
-Copy the original sheet and work on the renamed copy -Remove all cells with bold font text (these are titles) -Create a new sheet -Transform the table to 1 column in the new sheet (no order required) -Remove empty rows -Save as CSV files with 2950 rows maximum each, with same name+number
I have the below macro steps to save an excel sheet in PDF format in "C:UsersxxxxxDesktop" and with the name of the workbook.
But when the PDF report gets published in c: drive , the PDF file name has .xlsm added to it instead of just the file name. I would like to know how to publish the file in PDF format just in the name of the workbook.
Find the below macro steps:
Sub Docsave() Dim docname As String docname = ThisWorkbook.Name ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _ IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _ True End Sub
I am still a novice in you standards, but have run into a problem and hoping you all will have some insight for me.
I have a workbook that I am parsing data from the first sheet and putting the data onto the second sheet. Before I place the values in I want to format the cells with boarders, colors, alignment, and in some cases validation (but I can worry about the validation later).
Currently what I am doing is using a 3rd page that has a group of cells that have the formats I want in place before I start. The code will then parse the data and when it finds the data I need go to sheet 3 and grab a range of cells and paste those cells to sheet 2 and then write the data over those cells. This allows me to format the document as I create it.
The problem is that I am going through 25k of lines and on average this function is running 40+ minutes. If I run the same script with out the formatting it only takes at most 2 minutes.
So on to the question.
Is there a way that I can start of my script by grabbing all the format information before I parse the data and save it as a variable? Then instead of doing a copy of the format it would just apply the formatting that is saved already. I would think this would be much faster of a process.
