I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
When I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
Is there a way for the Macro code to bypass this issue?
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.
We have a spreadsheet that is sent to a manager weekly. The manager takes the sheets from 30+ individuals and copies all into a single workbook that is then distributed to a very large audience and reviewed weekly. In this workbook, I have created drop downs with conditional formatting - Low = Green, Medium = Yellow, Critical = Red. On the original workbook, this formatting works great, however, after the manager consolidates and redistributes all worksheets the Green shows Gray, Yellow is OK and Red shows Black.
My VBA code should post the formula "=IF($B6=""Store URL"",TRUE,FALSE)" into the conditional formatting formula box but everytime it pastes something different, such as "=IF($B11="Store URL",TRUE,FALSE)"
The full code is:
Sheet1.Select Set ConditionalFormattingRange = ActiveSheet.Range("B6:F505") ConditionalFormattingRange.FormatConditions.delete
I have figure A1 column which total amount accounting at cells A31. I need figure 360 as a benchmark calculation perfectly been done. In this calculation 360 well off inside cells A18 although actual number is 363. If we use Conditional Formatting when had enough total in cells the achieve 360, cells colour will be transformed to red. What formula which need I used to change no matter cells on A1 to A30 if sufficient study 360 automatically cells changing colour to red
Is it possible to format a row of cells (B7:KX7) depending on the value in column (G7:G1000) changing?
For example column G might have the following value entered into them.
A A A B B B C C C
So all the rows containing the letter A would be formatted one colour, then it would notice it changed to B and format all the rows with the letter B in, then C etc... The letters would always appear in blocks of 10 or more (representing students in a group) so it wouldn't go A,B,A,B,C,D,E
I am not bothered about the letter being tied to a certain colour, it just needs to change when the letter changes. I'd have no more than 10 different variables in this column, meaning no more than 10 different colours to format. Formatting would be black text with different coloured backgrounds.
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
Please take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.
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.
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain
Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...
Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...
I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...
I have a spreadsheet that uses VB macros to calculate sums of cells based on the font color of the numbers inside. It used to be fairly easy going through each cell and "classifying" them by color, so that my macros can go ahead and sum the numbers in each respective color's cell... but now I have a huge amount of numbers and would like to automate the process somewhat. Here's an example using the A and B columns:
flight $400 hotel $150 hotel $130 meal $20 meal $15 flight $350
I tried using conditional formatting to automatically change the color of the adjacent cells based on the presence of a keyword such as "flight" or "hotel", but this change is only cosmetic, and doesn't actually change the font color (it is still the default black, hence why my color-summing macros won't work!).
I'm including a sample macro for what I use to color-sum my cells, but what I am looking to automate the color-coding process based on looking for keywords as explained above in my example. Here is one of the working color-summing macros (for red, in this case) if you'd like to use it as a reference:
Function SumRed(SelectedCells As Range) ' Adds the values of the cells where the font colour is red(3). Dim Cell As Object Dim x As Double
I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.
I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"
The following is not working for me:
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim DistractorText As String Select Case Target.Address Case "$B$23" If Target.Value = "Distractor" Then DistractorText = InputBox("Type of Distractor:") Target.NumberFormat = "@" & " - " & DistractorText
There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.
In conditional format I've this formula =countif($Q$2:$V$2,B2)>0 which will return any matching numbers in yellow. My question is can I insert that once that row reaches 10 matching numbers the row turns green.
This should be pretty simple but I don't know how to do it. When I run the macro recorder, it doesnt record the change. I just want to change the number format of the chart labels from currency to a normal number and visa-versa. Why doesnt this work?
If I change the number of A2 to 0 decimals places, the value shown will be 2 (which is correct, of course), but the value of A3 becomes 20. I would like for this to stay at 15 because the correct value of A2 is 1.5, not the rounded value of 2.
Is it possible to format a cell based upon the format of another cell? For instance, if the cell to the left of a target cell is bold, is there a conditional formatting cell that can be used so that the target cell is bold as well?
I have a large spreadsheet with a number of columns that are set up for conditional formatting. For example a column of "BUY"'s and "SELL"'s where the "BUY"s are formatted with the default "light green fill, dark green text". I have other columns that are conditionally formatted using a formula. When I base the result on a formula I don't seem to have the option to use the default formats. Is it possible to use the defaults or maybe even how to customize my format to look like the default? It would look a lot better if I had a uniform red/green style throughout the sheet.
I have a worksheet with conditional formatting to shade (with the color grey) every nth column using the =MOD(COLUMN(),5)=0 function. I need to manually add cells formatted with various other colors to my worksheet. Some of the colored cells overlay the conditionally formatted shaded columns and the shading shows rather than the colors. I want colors to show on top of the conditional shading.
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.