Conditional Formatting For Lots Of Different Item Numbers?
Jul 13, 2014
What would be the best way to apply conditional formatting where you actually want to apply a background and foreground colour to cells that have many different item numbers, ex:
6201 = Dark blue font, light blue background
6202 = Dark Green font, light green background
6230 = Dark Purple font, light purple background
6245 = ...
6445 = ...
6447 = ...
6448 = ...
and the list goes on for about 200 different colour combinations.
I know I can do this in conditional formatting using the ribbon, but I wonder if there was a faster way to do this
I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.
I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula
I have a sheet that has conditional formatting that looks at column D2 for a number and divides that by half. it then colors in a corresponding cell on a row in the sheet that matches that number. the problem i am having is with odd numbers like 23 it gives a result of 11.5 . all my cell numbers are all rounded to whole numbers. i need to round the 11.5 to 12 or 13 it does not matter which one... i just need it to round it up or down. I have tried everything i can think of but have had no luck. I attached my workbook so you can see how it works.
Which gives me "1" in column "D" (last number in string)
In column "P" I have a number which I want to apply conditional formatting to, to highlight if the numbers in "D" and "P" do not match.
What is happening is that the conditional format is seeing the formula not the number and therefore always applies the condition. How do I correct this.
suppose I have the numbers 1-25 in a column. I want to color the numbers 1,4,7,10,13,16,19,22,25 green, color 2,5,8,11,...25 orange and color 3,6,9,12...25 red. I can not set up the conditional formatting formulas correctly.
I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.
If the cell value in column B is 2 or more, the same cell should be highlighted in Green.
If the cell value in column B is 1, the same cell should be highlighted in Yellow.
If the cell value in column B is 0, the same cell should be highlighted in Amber.
If the cell value in column B is less than 0, the same cell should be highlighted in Red.
If the cell value in column A is "Available", cell in column B should be highlighted in Amber.
I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).
I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".
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:
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.
I have a table that has labels of departments. Then each of those departments will be tracked per week on a number. I am looking for a formula for conditional formatting that if cell C5 is less than B5 that cell C5 (with the smaller number) fills green. Then depending on the next weeks number it will have no fill or a green fill if the number has decreased in cell D5.
Is there anyway that I can do an array of cells or do I have to do it individually per column?
i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
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.
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
I'm working from a sales ledger file whereby I want all invoice data in one row although the Item Number & Item Description are on the first row. Rows 2-10 (for example) are the actual details of the invoice: Invoice #; Date; Customer ID; Customer Name; QTY; etc.
Is there a formula or easier way than copying/pasting the "Item Description" to the first column (J in this case) without any related invoice data?
I want to send the value of selected item in texbox , the value format is change by combobox value ..and I want the value is fill on the next column of sheet ( see attached file please)
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 am looking for a macro that will combine qty's of item codes when first 6 out of 9 numbers in the item number match. Please see example of data below. In this case, the macro looking at the first 6 numbers in used and new. I should get a total of 26 - new and 45 -used.
A B C D E Item Name yard Item Numb Inv New Inv Used Frami panel 3x6 416-100 588801000 6 27 Frami panel 3x6 416-150 588801000 10 Frami Xlife 3x6 416-100 588801500 20 8
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.
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...
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.
Lots of XLS files in a directory (C: emp). Each book has only a sheet1 with the data.
I have another workbook that i need to use to overwrite certain values in one column from each book.
The workbook I have has a column A and B, ID and value.
In each file inside the directory, A3 to last row used in A had each ID. If the id matches to an id in the workbook with the new values, I want to take the value from B in the override table and put it in C in the file. Look at each ID in each file against each ID in the override, replace and close.
VB: 'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected) Private Sub ListBox1_Click() ListBox2.ListIndex = ListBox1.ListIndex End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
