Color Table Rows Where Specific Cell In Row Matches Condition
Jul 2, 2008
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C.
Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row.
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1) . Interior.ColorIndex = 16
View 4 Replies
ADVERTISEMENT
Mar 10, 2014
If I have these on Sheet 1
Name Age Cutoff
PersonOne 27 21
PersonTwo 21 20
PersonThree 19 19
PersonFour 17 20
generate/list/display on the next sheet (Sheet 2) of all the rows that has AGE less or equal to Cutoff, so on Sheet 2 based on the Sheet 1 data, I should get the following rows listed:
Name Age Cutoff
PersonThree 19 19
PersonFour 17 20
View 10 Replies
View Related
May 12, 2007
I searched through the files and found a macro that works for me to color code my spreadsheets in Excle. Hwoever. How can I set it to stop when Cloumn G = the word "Created"?
Sub prettyPattern2()
Dim lRow As Range
For Each lRow In Range("A6:K500").Rows
Select Case lRow.Row Mod 2
Case Is = 1: lRow.Interior.ColorIndex = 34
Case Else: lRow.Interior.ColorIndex = 35
End Select
Next lRow
End Sub
View 8 Replies
View Related
Feb 4, 2008
to shade rows of data in a table if a cell contains a specific value, such as the word Total.
View 9 Replies
View Related
Sep 24, 2013
I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)
When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.
I need the text in column AQ (the monetary amount) to become red when the the opposite is true.
i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.
View 2 Replies
View Related
Aug 21, 2008
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies
View Related
Mar 22, 2007
Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -
View 5 Replies
View Related
Oct 26, 2007
A friend is trying to change an entire row's color based on a specific cell's value in that row. He cannot use conditional formatting. This is the code he's tried, to no avail:
Sub temp()
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 25).Value = 4 Then
Rows(Row).Select
Selection.Font.ColorIndex = 3
End If
Next Row
End Sub
View 2 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
Mar 22, 2011
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2.
Sheet 1 contains data in columns A to H.
Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
View 4 Replies
View Related
Oct 3, 2006
I need to change automatically the color of a cell (let say A1) to green if the cell content is YES and to red if content is NO. Which function to use?
View 2 Replies
View Related
Sep 15, 2007
Using conditional formatting is it possible shade an entire row when a cell is = to 0. I am currently creating a spreadsheet for a small amount of inventory. I am manually editing the amount of product available.
What I want to happen is when I replace them number with the text ‘Sold Out’ I want then entire row to automatically grey that row out. Is it possible to make this happen using conditional formatting?
View 9 Replies
View Related
Apr 29, 2008
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies
View Related
Mar 2, 2013
fill color of autoshapes on condition depend on cell value
View 1 Replies
View Related
Aug 4, 2014
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?
View 5 Replies
View Related
Aug 4, 2007
I currently have a report that I have code on that will change the color of a cell based on whats in the cell.( Code posted below) However I also need to look at $J and if the first letter of the value in that column is a "z" then I would like to change that whole row to icolor 13 (purple).
For example J14 might be "Z-Thomas". if it does I would like that row 14 to change to purple.
Also I would prefer that the solution be in VB, so that we do not have to do something in VB with the fix below AND do something in Conditional Formatting.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
Case "?"
icolor = 6
View 9 Replies
View Related
Jun 23, 2009
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.
Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.
View 3 Replies
View Related
Sep 17, 2012
I have a conditional cell that change color or not if condition...OK so far. Now, how can I change the color of a range of cells when condition is in just one cell. Ex:
If C15>=150, change (C3;C20) to yellow.
I really can't find the way to do it.
View 2 Replies
View Related
Apr 16, 2014
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
View 2 Replies
View Related
Sep 12, 2007
I am currently doing a spreadsheet and require cells in column f (which have monetry values entered) to colour in different colours depending on whether there is a yes or no value in column e.
View 2 Replies
View Related
Sep 6, 2006
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
Sub DeleteRepeatedRows()
' goes through coord. sheet and identifies duplicated lines, copies the date from the old row
' copies it into the new line, then deletes the old line
Dim rCell As Range
For Each rCell In Selection
If rCell.Value = rCell.Offset(1, 0).Value Then
rCell.EntireRow.Delete
End If
If rCell.Value = rCell.Offset(2, 0).Value Then
rCell.EntireRow.Delete
End If.........................................
View 7 Replies
View Related
Nov 26, 2009
Is 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.
View 6 Replies
View Related
May 27, 2014
I have the following code from a previous program I made and need converting it.
[Code] .....
I want it to do the same thing except it will only add a dropdown menu for a Cell that has a background of Yellow.
View 2 Replies
View Related
Sep 3, 2006
Example:
On sheet "Inventory List" is the value there is a value in cell A2. If that value is found in sheet "FHS Locker List" I would like the box highlighted. (I can do the highlighting) I would like to copy the formula down for cells A2-A900. It would be looking in cells K3-K900 in the "FHS Locker List" sheet.
View 5 Replies
View Related
Mar 24, 2009
I need to be able to assign colors to cells depending on what text is inputted.
If a name start with letters between:
A - Cald (I want the cell to be Yellow)
Call - Eg (I want the cell to be Black)
Ek - Hall (I want the cell to be Red)
Etc.....
View 9 Replies
View Related
Mar 18, 2010
Two part question:
1. How do I structure an IF statement based on a specific cell color (e.g. if a cell is yellow, then perform function x)
2. How do I know what color is what? Is there a pantone reference? A color "name" that excel uses?
View 9 Replies
View Related
May 12, 2006
I trying to look for a all the cells with the same fill color in the same column and assign a specific text to the cell for example
Column A - Cells with fill Yellow must have for text the word Sub- Total
Column A - Cells with fill Black must have for text the word Grand Total
View 4 Replies
View Related
Apr 17, 2008
I've been trying to come up with a conditional that will format a row if a cell contains any instance of a given word.
I know the following will format rows containing the EXACT entry of "apple" in column E:
=$E1="apple"
But I'd like a conditional that will find entries like "apple pie" and "crabapple" as well. I'm sure it's a simple solution, using a SEARCH function or something...but I can't figure it out.
View 3 Replies
View Related
Oct 22, 2012
I have been wracking my brain around how to put this formula together.
Is it possible or it can't do more then 1 cell at a time?
Here is an example:
If F1= button request and E1= Scoring then OK (take no action) also i want to add that Folder in F and Corrupt in E would = OK
If it doesn't match to highlight the cell in red
View 8 Replies
View Related
Jan 20, 2014
I am using a pivot table in excel 2010. 15 columns (fixed) and plenty of dive downs for rows.
I am trying to 'hide' all of the rows containing values less than 10. If it is easier we can start with hiding values of zero and go from there.
It is key to hide the rows as within the dive downs the rows within the pivot are to many to sort through. If there is a way to do this using the grand total columns for each row that would work as well. Just to be clear I need to hide the entire row not just report the empty cells as zeros. If any further detail is needed feel free to ask.
For example: If value of column O "Grand Total" = 0 'hide rows with value 0' ELSE 'display rows with value greater than 0'
View 4 Replies
View Related
