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 Repliesi'm trying to figure out a conditional formatting that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
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 RelatedUsing 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?
fill color of autoshapes on condition depend on cell value
View 1 Replies View RelatedI 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
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.
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.
I got 2 colums:
Column A: Column B:
EUR 1.00
USD 2.00
USD 3.00
EUR 4.00
As you may have guessed I now want Excel to check in column B the content of column A and to change format accoring to content:
Goal:
A B
EUR 1.00 €
USD 2.00$
USD 3.00$
EUR 4.00€
I want to stress, that a solution using macros isn't possible.
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
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI 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 Relatedis 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 RelatedI have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red
blue
yellow
green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
How to format a cell to be normal color unless cell left of it is not empty and this cell is empty.
And I need to format whole sheet to this rule.
In a cell I have this formula to tell me what day of the week the previous date falls on. =TEXT(J77,"ddd")
I want to know how i can format that column so that weekends are in red.
I'm trying to format the color of a certain cell, if two values match.
A1=100
B1="Choose"
I want to format B1 to color RED, if A1=any number, and B1="Choose".
B1 contains list-data where you can chose from different values.
So if A1 contains a value, and B1 has not been set a different value than "Choose", format B1 to RED. Which would indicate that you have to set a value for the rest of the worksheet to work.
I have a list of records that will eventually be used to create a pivot table. Sometimes an account number will be listed twice but instead of adding to the previous info, it needs to start a new record. I decided in these instances to mark the account number with a character (+). Even though the + will designate a separate record, I'd like it to be invisible.
Is there a way to automatically turn the font color white for only the +? Since I've automated the rest of the process using a macro, a VBA solution is fine.
I have a column of data that contain miscellaneous comments in each of the cells in column 'A'. This is a sample of the text in one of those cells:
The quick brown fox jumps over the lazy dog.
Is there a way to programmaticly search each cell in col 'A' to look for any occurrence of the string 'fox jumps over' and highlite in RED only that string occurrence?
So in the end the above cell would now be:
The quick brown fox jumps over the lazy dog.
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 3 Replies View RelatedOn the attached, I want to make the status 3 lines conditional on the text found in another cell.
View 5 Replies View RelatedI have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters.
Samples of what I've tried:
=IF(LEFT(L23,1)=P)
=IF(MID(L23,1,1)=P)
etc.
I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
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.
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
I'm trying to search a column for the first occurence of a given text string, get the cell's row (that's the hardest part for me), and color that row.
I've tried using the macro builder, but I can seem to get the referencing right and get different error (91, 424, 1004), depending on how I've managed to mangle my code at the time.
Here's the current version of a bit of my
Set rFoundCell = Worksheets("WBS").Range("B1")
Worksheets("WBS").Columns("B:B").Select
Set rFoundCell = Selection.Find(What:="Insert all new lines above this line by insert button" _
, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:= _
xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:= _
False).Activate
' how do I get the found row? or do I need it ??
With rFoundCell
rFoundCell.EntireRow.Font.ColorIndex = 35
End With
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies View RelatedI would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:
1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.
2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.
3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.
I have a worksheet called "Mgr Scorecard". Within this worksheet across the top in row 1 are the Manager names. In row 2 are all of the items for each manager that they are score them on. In column A is a list of our EE's. Based on how they score the cell gets filled in either red or green.
For my example below I am looking for a macro that I can tie back to a command button so that when the button is clicked the macro go out looks at a column "Item 1" for each Manager and for all those cells shaded "green" returns those rows. I know this can be done in conditional formatting but due to the large amount of data to sort a macro linked to a command button will be easier.
So on my example below the macro should return EE 2 & 4 since they are the only two shaded green. I wasn't able to get the shade colors on this after I posted so I just type in what color they would be, but on my actual sheet there is no words just colored cells.
Mgr Name
Item 1
Item 2
Item 3
Item 4
EE 1
Red
[Code]....