There is a table [A8 TO D60] and a column alongside, where the months of the year are listed. In a particular independent cell, the name of any month can be chosen and entered.
The table rows preceding the chosen month, have to be shaded. When a different month is chosen, the shading should now cover the new set of rows preceding this different month and so on.
I'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.
The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.
I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.
I now want to count how many of each colour there are in each row to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.
I would like to change the color of a cell to either green, yellow, or red based on a few conditions. One of the conditions would be based on whether a check box was checked or not.
View 2 Replies View RelatedWe 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.
View 2 Replies View RelatedMy 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
[Code].....
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.
View 4 Replies View RelatedIs it possible to check the conditional state for a specific condition for a specific cell.
For example a cell has 1 or more conditions.
A condition will toggle to a condition if true or false etc.
The specifics is i use red (color 255) if a cell has not met a specific condition (of 1 or more conditions)
So if the cell is mandatory to be populated, and is blank, it will be red, if not it will be something else.
But i want to loop all visible cells with conditional formatting, and where the cell has a condition when true will show interior color = to 255 return the cell address.
To msg the user where data entry is still required etc.
I have a horizontal bar chart in which some of the bars represent positive values and others represent negative values.
How to format the bars so the "positive" bars are shaded in one colour and "negative" bars in another colour? I'd prefer not to change the bar colour manually as the values change frequently.
If a cell cntent includes a symbol [as an arrow, for instance] beside the text,the font colour does not change as required when the condition is met.
View 2 Replies View RelatedI 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
View 5 Replies View RelatedHow 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.
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)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
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
[Code] ...........
I'm trying to create a formula in 1 conditional format rule. The goal is to check if al cel contains "1" or "Z". If the value is "1" the cell must turn black and if it is "Z" it turns red.
If this is not possible, I'd like to only turn it black.
I am not able to use vba and have only 1 conditional formatting rule left in my excel 2003 sheet.
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
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???
I have the following code which prints the number of time steps required and displays it as t0, t1, t2 etc how can I make all the numbers subscript? i.e until the number of time steps = the total time.
View 2 Replies View RelatedIs 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...
View 9 Replies View RelatedI would like to format a number of cells depending on the percentage of another cell.
I have a cell 'Seats" that has a number in it. In other columns, 'Stop1', 'Stop2', 'Stop3', are vaious numbers. I would like to format the Stop cells according to how close (or over) the number is that is in the 'Seats' cell. For example, if Seats is 30, Stop 1 is 15, it would be a medium red. Stop 2 is 30 (or over), it would be red. If Stop 3 is 5, then white. Basically a percentage scale of color depending on the number in the Seats field.
I have a workbook (column K) set with the following format conditions:-
Cond.1 - =(I3="declined")
Cond. 2 - =(G3>0)
Cond. 3 - =(K3
I am using the following code that I got from this forum to color tabs. But I can only get it to do two colors. Is there a way to get three conditional colors of tabs?
Private Sub Worksheet_Change(ByVal Target As Range)
With Me
Select Case .Range("D13").Value
Case Is < 20
.Tab.Color = vbGreen
Case Is > 20, Is < 30
.Tab.Color = vbBlue
Case Is > 30, Is < 100
.Tab.Color = vbRed
End Select
End With
End Sub
im using a vba macro to open a workbook and bring values into specified cells in the current workbook.... however when i do this its changing the cell formatting and then the info is coming up as dates instead of how it should look, is there a way to stop this? here is my code....
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
'On Error GoTo error1
Dim RunNum As String
RunNum = Range("N9").Value
Set wb = Workbooks.Open("X:FrontEndChip FabFacet CoatingFinished Eddy Runs" & RunNum & ".csv")
With ThisWorkbook.Worksheets("Sheet2")
.Range("C3").Value = wb.Worksheets(RunNum).Range("C3").Value 'run#
.Range("G3").Value = wb.Worksheets(RunNum).Range("G3").Value 'Date
.Range("J3").Value = wb.Worksheets(RunNum).Range("J3").Value 'Operator(s)..................
NOTE* the info being read into the worksheet looks like this 12-2 or 10-1 or 12-12 but if it would stay as TEXT like i have it set to instead of changing itself to date format it would work just fine
I'm referring to a post I tried to solve:http://www.excelforum.com/excel-gene...ml#post2227328
NO need to read. Try the attachement instead.
Steps done:
Select Column D
CF function =D1<A1
Select Column A
CF function =A1<D1
If I select cell D3 and look at the CF then I see:
CF: =D1<A1
where I would like to see
CF: =D3<A3
I remember this was the case in <=XL 2003? (now using 2010 beta)
VBA code to disable(grey out) condition formatting command in ribbon. Because I generated a template and do not want user to change the condition formatting.
I have tried below, but it did not work...
Application.CommandBars(1).Controls("Format").Controls("Conditional Formatting...").Enabled = False
I have two worksheets. One is called "Orders" and the other is "Invoice2".
On my "Orders" sheet I have a listed of product codes and ordered quantity. One column being ordered quantity and the other being product codes.
For instance it is like this:
Product Codes Ordered Quantity
TH1 5
AB3 0
JC5 10
LFK 11
On my "Invoice2" sheet I have a column for product codes and ordered quantity. Basically, I want the "Invoice2" sheet to reference the "Orders" sheet in relation to these columns. However, on the invoice I only want to display the product codes ordered and ordered quantity. For instance TH1 would be the first line and JC5 the second line. This is because AB3 is less than 0. So ever product code on the "Invoice2" sheet must be greater than 0 in ordered quantity on the "Orders" Sheet. This is what the invoice2 sheet would look like:
Product Codes Ordered Quantity
TH1 5
JC5 10
is there any way to set the conditional format to the colour of a particular cell if the condition is met?
View 9 Replies View Relatedi 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.
View 2 Replies View Related