Color Row Based On Fixed Cell & Relative Cells
My sales spreadsheets have a column which is turned to 100% when the order comes in (i.e. when the salesman gets it in his commission). I have applied conditional formatting to turn the whole row green when this happens (for ease of seeing which orders are in)
We also have a cell for the Purchase Order "number" (as these come from the customer, these can be a straight number sequence - "12345" or a mix of letters and numbers - "ABC12345"). My boss would like this cell to be red if the "order in" column is at 100%, but there is no PO number.
However, when I put in the formatting:
(1) =$K$15=1 (to turn the row green)
(2) Cell Value is equal to 0 (to turn the cell red)
it will apply (1) no problem, but will only apply (2) if (1) is false. (i.e. if (1) is true, the whole row goes green, with no red in the PO box.)
If I switch them round (i.e. have (2) first), then I get the formatting that I want (i.e. green row with a red box), but if (1) is false, I still get a red PO box.
What I need is a way of only applying (2) if (1) is true. (or any other way of only making the PO box red if the order is 100% but there is no PO number)
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Color Code Cells Based On Value In Row 1 And Value In Cell
I have 16 columns (B:Q) Row 1 contains either a number, the letter x, or the letter a. Cells in Range("B2:Q628) contains either the letter x or is blank If a cell in row 1 contains an x, then all cells in that column with an x has an Interior.ColorIndex of 45 If a cell in row 1 contains an a, then all cells in that column with a blank has an Interior.ColorIndex of 45 If a cell in row 1 contains has a number, then nothing
View Replies!
View Related
Color Cells Based On Color Of Preceeding Cell
I am looking for a very simple script that will achieve the following: On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order: From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
View Replies!
View Related
Returning A Row Of Cells Based On A Color
I am having a bit of a problem writing a formula that works under the specific conditions that I need. I have a range of 6 cells, A1:A6 which contain text I have formatting that turns that row red based on specific conditions. The problem is that if A1:A6 is red, I then need the text in those cells to be copied to Sheet 2 B1:B6.
View Replies!
View Related
Color Row Range Based On Cell On Same Row
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO. Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color. Attached is an sample of what i want.
View Replies!
View Related
Change Cell Color Relative To Another
I am in the middle of my ICT A-level coursework and im required to design a spreadsheet with a grade system (A to F). The students have individual target grades and recorded grades from tests and homework will also be dispalyed. How do i change the colour of the cells of tests or homework depending on what target grade the student has. I will have to do this for about a hundred students, so conditional formatting isn't really an option to do each individual cell. I'm not familiar with coding, but can learn. Not sure if a formula can solve this either. I have used the search function of the forum for this question but have not found anything regararding this.
View Replies!
View Related
Insert Row At Active Cell With Formula From Fixed Row
I want to insert a new row that contains the formulas of a fixed row (1:1). The inserted row is changeable and is determined by whichever is the current active cell. Eg: Active cell is something random like E16 I want to add a new row but don't want a blank row - rather want a row that contains the properties of 1:1
View Replies!
View Related
Delete Entire Row Based On A Cell Color
I used this macro to find the duplicates in column B: Sub KryDups() ScreenUpdating = False FirstItem = ActiveCell.Value SecondItem = ActiveCell. Offset(1, 0).Value Offsetcount = 1 Do While ActiveCell <> "" If FirstItem = SecondItem Then ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0) Offsetcount = Offsetcount + 1 SecondItem = ActiveCell.Offset(Offsetcount, 0).Value Else ActiveCell.Offset(Offsetcount, 0).Select FirstItem = ActiveCell.Value SecondItem = ActiveCell.Offset(1, 0).Value Offsetcount = 1 End If Loop ScreenUpdating = True End Sub The duplucate cells are now red in color. (RGB(255, 0, 0)). How do I now code VB to delete the rows in column B where the cell color is red? Here is some of the code that I tried:................
View Replies!
View Related
Format Cells Color Based On 1 Cell
I want to format a group of cells color based on the value of one referance cell. Example would be: If a1 is adn "X" the the results in cells b1 thru b10 would be green else they would be blue. Is there a way to do this using Excel 97?
View Replies!
View Related
Format Color For Range Of Cells Based On Cell Value
I have been tasked with creating a Macro in order to help speed up a rather simple set of steps that are taken to format a spreadsheet each day. The last step, however, is not so simple. I would like to add a statement to the macro code that does that following: Based on the value of a specific cell, color the cell and all corresponding cells within the same row (from colums A to AA) yellow. Essentially, cells in column D may have a the value "No Allocation", and if they do, I will need to format that cell yellow as well as all the cells within the same row for the colume range A to AA.
View Replies!
View Related
Change Color Of All Cells On Sheet Based Off Of One Cell Value
I want to change the fill color of all cells on my sheet, based on the value of one specific cell. In my sheet, I am using cell F1 as the trigger for the change. If the word Blue is in the cell, I want the background color of all cells to be Blue. Likewise for Red and Yellow as well. I don't believe conditional formatting can get this done, as all but the one cell (for this) will be empty.
View Replies!
View Related
Format Cell Color Based On Others Cells Not Being Blank
I know that I need to use Conditional Formatting, but I can't get the formula code correct. This is what I need it to do: If cell A2 is populated (not blank) AND cell B2 is blank, then cell B2 to be highlighted in bright red fill. If cell A2 is populated (not blank) and cell B2 is also populated (not blank), then leave formatting as per normal If cell A2 is blank then leave formatting as per normal Basically, as soon as someone types anything into cell A2, cell B2 to turn red. Then once they have input something into cell B2, the red highlight can disappear. The inputs will be text, not numbers, so I can't use </> I have tried varying circumstances of IFs, ANDs, NOTs, ISBLANKs etc and I can't crack
View Replies!
View Related
Color Column Charts Based On Font Color Data Cells
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1. After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table. I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View Replies!
View Related
Macro To Color Cells Based On Their Font Color
I have a spreadsheet that i download from the net daily, which is seperated into columns of information. I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red. For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc. If the font colour in a1 or a2 ... etc etc is any other colour then do nothing. I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
View Replies!
View Related
Countif Based On Relative Cell
I have this forumula in a cell: =SUM((COUNTIF(F7:F69,"<=" & TODAY())),(COUNTIF(B7:B69,">=" & D7))) and the trouble I am having is that in the second Countif, I need it to check the cell in the "D" column relative to what cell is being checked in the "B" column. Right now it is checking every cell in "B" against "D7" instead of "D8, D9, D10", according to what cell in "B" is selected.
View Replies!
View Related
Add Relative Row Formula To Cell
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2 Row 3 =A3+B3 However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)
View Replies!
View Related
Return Relative Cell Based On Condition
I have 8 columns of data, the first 4 and the last four columns have the values which correspond to each other.. Here is an example: VALUES(A to D)NAMES(A to D) ABCDABCD 1.3451641.3383581.286221.359808321325370391 So value 1.345164 corresponds to 321 and 1.28622 to 370. I need a function that will return a value from one of the VALUES columns which corresponds to the second smallest cell value from the NAMES columns. For example – here the second smallest number in NAMES column is 325 so the function would return the corresponding value of 1.338358.
View Replies!
View Related
Color Cell Fonts Based On Text Color Of TextBox Controls On UserForm
I have got a userform with lots of controls, One of the action's on a large group of the controls is the same but except for one number here is an example If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3 If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3 If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3 This makes a cell that correlates to the textbox red if the text in the textbox is red. Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop. Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
View Replies!
View Related
Setting Color Of Range Based On Adjacent Cell Color
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from. I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors. The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed. What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color. Here is the code I am using to set the color of the cells in Column A:
View Replies!
View Related
Change Font Color Based On Adjacent Cell Color
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View Replies!
View Related
Change Text Color Based On Cell Color
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white. i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
View Replies!
View Related
Formatting Row Of Cells Based On A Cell Content
I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person. I am almost certain that is going to take a VBA, but I know little to nothing about setting one up. I have attached a sample. I am using Excel 2003.
View Replies!
View Related
Color Cells Based On Adjacent Cells Being Empty & Add Text
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!). What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out. I have posted a link to an image which shows what I want. I hope I've explained it well enough! http://img530.imageshack.us/img530/6239/excelspfk0.jpg
View Replies!
View Related
Color Rows Based On Row Citeria
I have a range of data 9cols x 600rows which I want to apply 9 conditional formatting styles to. The range has other data in subsequent columns that I don't want the formatting applied to. The condition is quite straight-forward, but I'm damned if I can find out how to do it. Only a single cell on any given row will contain data (it's a error if there's more than one value on the row). Based on the column the data is located in, I'd like to assign a background colour to the segment of the row being analysed. Diagramatically (where * = blank) Data (Action) * * * * * * X * * (Colour this row segment some colour) * X * * * * * * * (Colour this row segment another colour) * * * X * * * * * (Colour this row segment another colour) * * * * * * * * * (Colour this row segment another colour) X * * * * * * * * (Colour this row segment another colour) X * * * * * * * X (Colour this row segment red - error) This is as far as I've got and it's not working. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim cell As Range
View Replies!
View Related
Conditional Formatting - Color Groups Based On Row Value
This is my problem: A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less. Every time this number changes, the color for the entire row should flip between yellow and no color. Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.
View Replies!
View Related
Hide Row Based On Color Column I Being Present
a way to hide used rows if the color of the cell in column I is green( color 35 vba) but only if the cell is visible will the macro change the rows visible state, in other words already hidden rows will remain hidden regardless if they match the color requirement. below is some pseudo code to try to explain better what I'm trying to do. The below code doesn't actually work but I was wondering if someone knew some code that would actually do what I've described.
View Replies!
View Related
Looking Up Column Header Value Based On Color And Position In Row
I am trying to create a schedule using colors. The colors will indicate the start time, break time, lunch time, break time (second break) and end time. see attached document. Thank you for attempting to solve this puzzle using formulas and or VBA code. I am trying to automate the schedule so the numerical times automatically appear when I "paint" the schedule.
View Replies!
View Related
Color Cells Based On Multiple Conditions Of Different Cells
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it! Condition and Results required would be: IF column C = “S” than on Column B = color cell light blue with white border IF column C = “P” than on Column B = color cell Green with white border IF column C = “A” than on Column B = color cell Yellow with white border IF column C = “L” than on Column B = color cell Red with white border IF column C = “C” than on Column B = color cell Dark Blue with white border IF column C = “ ” than on Column B = color cell white IF column C = “V” and has white background than on Column B = color cell white IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey I don’t know if the last two are feasible.
View Replies!
View Related
Color Cells Based On Others
I have a long standing problem, i need to use some code that will search a range of cells , say j1 to j15, for a text "ship", gather the value from the equivalent h column cell and then use that value in futher code to colour some cells. So if the value returned is say 5, then colour the next 5 columns from column A whenever "ship" is typed.
View Replies!
View Related
Color Cells Based On Value Above/Below
I have many cells in a column, where I would like to change the colour depending on the value in the cell above. If one cell is more than or equals 600 000 and the cell below is less than 600 000, then the lower cell should change colour to red. This should be repeted for all cells in the column, until the last empty cell. Sub macro() x = 7 Do While Cells(x, 4).Value <> "" If Cells(x, 4).Value >= 600000 _ And Cells([x + 1], 4).Value < 600000 Then _ Cells([x + 1], 4).Select _ Selection.Font.ColorIndex = 3 End If x = x + 1 Loop End Sub For this code I receive the error code "Compile error: End if without block if".
View Replies!
View Related
Color Cells Based On Difference Between Two Cells
I have a 2 lists of manually entered values where the values in each row are related. They are related in the sense that if the difference between them is equal to or less than, a given amount then the cell containing one value is colored Blue. e.g. COl A holds Value x while COl B holds value y. I need to check which value is entered in columns A and B and if a given difference or greater exists colour the cell in COl B. For example the relationship relevant to a COL A value of 21.0 is 11.0. So, I enter the following; A1 B1 21.0 13.0 <----------------No color Blue I enter new values 21.0 9.5 <----------------B1 is blue.........................
View Replies!
View Related
Copy Cells Based On Color
i have att a workbook with 2 command buttons on sheet "quote" one turns the selected cell red. the other one i would to have select the red cells then copy to sheet1 then change the color back to white
View Replies!
View Related
Color Cells Where Column X Same Row Has Text
I've got a spreadsheet where there are three columns (a-d) Now B-D contain dates and I'm looking for A to be coloured conditionally so if column B contains any text, then A's background will be red, if A & B contain text, then it'll be orange and if A & B & C contain text then it'll be green. What I can't seem to work out is how to get this to work in this order only - ie my version with conditional formatting means if someone entered text in D and nothing in B & C it'd turn Red...
View Replies!
View Related
Count Cells Based On Font Color
Hi, I've been cracking my head for a few days your assistance is much appreciated. Name SA1SA2SA3SA4SA5AYESNONOYESYESBYESNONOYESNOCYESNONOYESNODYESNONOYESYESEYESNONOYESYESFYESNONOYESNO The ones in red is conditionally formatted. So I want at the end of each row to count how many are in red.
View Replies!
View Related
Macro To Color Cells Based On Criteria
Column DColumn EColumn FTarget %DifferenceCash4%5.12%Large-Cap8.5%1.73%Commodities4%-1% I need to color code Col F based on the following criteria: If F2/E2>20%, then shade pink else no color. I only care about the absolute values. The 20% number can change depending on the category. So for commodities, the limitation is 30%. Like that I have a list of about 20 asset classes, 14 of which have the 20% constraint, rest are either 25% or 30%. I would like the cell color to be updated each time I import a file. Also I have other conditional formatting on Col F (like red color if negative number) so I don't know if another conditional format will work on the same cells?
View Replies!
View Related
Copy Cells Based On Fill Color
i have some problem in display the highlight cell to another worksheet. Below is in module, i would like to display the highlighter cell to another worksheet. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 9/2/2006 by user ' ' Range("I351").Select ActiveWindow.SmallScroll Down:=-12 Range("I349").Select ActiveWindow.ScrollRow = 1 Range("E1:E400").Select
View Replies!
View Related
Deleting Cells Based On Font Color
I am trying to delete certain cells within a range of data (A13:E29) that have white font or #N/A's. The only information I need is the black data. I have to manually go and clear these cells for many different ranges. I'd like to incorporate a macro that analyzes the range first to delete the values that are white font or #N/A.
View Replies!
View Related
Sum Cells Based On Color Index
I am trying to use a function kindly listed by Dave, that allows for a SUM to be done on cells which have a certain color index. I have modified it very slightly, but unable to set the call to it without getting errors, perhaps someone can shed some light on what i am doing wrong. Dave's original code is Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell,vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function.......
View Replies!
View Related
Color Cells Within Matrix Based On Conditions Outside
In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'. Each factory has its own worksheet as well. In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT. I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid. This is the formula: =IF('MAIN REPORT'!I2="","",1) However, this simply checks that data while there is only 1 quantity for each row of each house. Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then, If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.
View Replies!
View Related
Color Cells Based On Entry. More Than 3 Conditions
I was hoping there was an alternative to having to use so many named ranges for each sublist (there would many sublists in the actual workbook). Anyway, I'm a little uncertain about the code you posted - do the LIST and SUBLIST codes go in Source in Data Validation, or in Source in Dependent Validation Lists Based On Header Values? Can you give me a bit more instruction on this? Also, I'm trying to use the following code to color cells upon a change in value in V1 or V2, but can't get it to work. Can you kindly tell me what's wrong with it? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "V1" Or "V2" Then Exit Sub Select Case [V1].Value Case "A" Target.Interior.ColorIndex = 40 Case "B" Target.Interior.ColorIndex = 35 Case "C" Target.Interior.ColorIndex = 36 Case "D" Target.Interior.ColorIndex = 34 Case "E" Target.Interior.ColorIndex = 19 Case "F" Target.Interior.ColorIndex = 24 End Select End Sub
View Replies!
View Related
|