Conditional Formatting - VBA - Cell.value VS Selection.formatConditions
Jan 12, 2010
I'm having major issues getting this to work correctly. Is there a way in VBA to code conditional formatting so the entire row of a range's color is changed if the condition is met? I have about 7 different colors I'm using, but if I use the following code (only partial is shown below), it only changes the specific cell (in my case, in column A), and I want the entire row (say, A4-AI4) to change colors, dependent upon what is in cell A4.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set MyPage = Range("$A$4:$AI$33")
For Each cell In MyPage
If cell.Value = "LH_SAS_1603" Then
Selection.FormatConditions(1).Interior.ColorIndex = 40
End If
If cell.Value = "LH_SAT_1603" Then
cell.Interior.ColorIndex = 37
End If
If cell.Value = "MH2" Then
cell.Interior.ColorIndex = 39
End If
Next
End Sub
I also tried the following, but it never stops looping through since I obvi didn't code it correctly:
Range("A4:AI33").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$4=$A$37"
Selection.FormatConditions(1).Interior.ColorIndex = 37
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$4=$A$39"
Selection.FormatConditions(2).Interior.ColorIndex = 40
Range("A37").Select
End Sub
View 9 Replies
ADVERTISEMENT
Mar 20, 2013
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???
View 3 Replies
View Related
Jul 28, 2009
I would like to format cells with a currency based on a value selected at the top of my worksheet. Cell C3 is a drop down of currencies. And I would like cells range E11:E200 to format in whatever currency selected in cell C3.
View 9 Replies
View Related
Jan 8, 2009
A1 is not in any pattern colour. But if A1 is changed into, say, Red, B1 will be equils to D5+F5. Otherwise if A1 is changed into Blue, B1 will be equils to D6+F6. Can it been done by Excel 2003?
View 2 Replies
View Related
Jul 7, 2007
I have a conditional format in Cell J3 that has 2 conditions as follows:
Condition 1: Cell Value, Equal to, ="NO", and then the format will color the cell RED.
Condition 2: Cell Value, Equal to, ="YES", and then the format will color the cell GREEN.
I want Condition 3 to turn the cell J3 YELLOW if there is ANYTHING at all or anything above "0" typed into Cell D3.
View 9 Replies
View Related
Oct 9, 2013
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies
View Related
Apr 12, 2014
I have a formula as follows:- B20 is the cell I wish to format. The formular is =B18-400. My problem is I wish to hide the -400 from the cell until it changes.
View 5 Replies
View Related
May 22, 2014
If the value in column AE falls below 100 it needs to turn yellow. If the value drops below 50 it needs to turn red.
But if the figure in column AB is 0 then i dont want it to do anything at all.
View 2 Replies
View Related
May 29, 2014
how to highlight a particular cell red if the value in one cell is less than or equal to the value in another cell. For instance, How do I make it so that the "Hours to go" cell turns red if the value in the "Actual Last Completion" value is less than or equal to the value of "Last Completed" cell?
View 3 Replies
View Related
Jun 3, 2014
i want to calculate percentage between A and B and highlight top 10 values in B.
as of now i am calculating % in C and applied conditional formatting in C. filtering C based on color i am formatting B manually.
is there any option to avoid this manual work.
Total countAlarm reported%
562545%
783545%
322578%
View 3 Replies
View Related
Jul 29, 2014
In Excel, I want to do conditional formatting on a cell based on the value of another cell. For example: in cell A1, I have text. I want this cell to have a grey background if the value of cell B1 is less than or equal to 1/31/14 and to have no fill (default) if it is greater than that date.
View 8 Replies
View Related
Aug 25, 2014
In the attached, I want cells to be highlighted red if they're NOT EQUAL to 0. Why is this conditional formatting not working (i.e. the cell is being highlighted)?
Conditional Formatting Example.xlsx
View 5 Replies
View Related
Apr 9, 2014
I am working in a table where I would like the whole row to highlight based on the value in one of the cells. These rows of data will be updated regularly, so the formatting has to change with the data.
View 13 Replies
View Related
Aug 21, 2014
This seems way too simple to do yet I can't get it right. It will highlight some that are different and not highlight others that are different. I need to compare column A to O and highlight differences, B to P and highlight difference, and so on.
View 2 Replies
View Related
Oct 3, 2008
Is it possible to use conditional formatting on a cell if it contains comments.
View 2 Replies
View Related
May 19, 2009
I am using the following formula on a conditional formatting to color cell A1 based on cell N5 date =$N5=TODAY()-2
The problem is that I need this formula to consider only weekdays, how can apply formula weekdays to my formula?
View 6 Replies
View Related
Jul 8, 2009
I want to use conditional formatting on a column ('A') to highlight cells that do not have a match with any cells in a specific range.
So I've made a range called 'AcceptedData', which contains a couple of cells with text.
Also included a blank cell in the range, since I do not want blank cells to be highlighted.
Then I used this conditional format forumla on column A.
=COUNTIF(AcceptedData;A1)=0
But it doesn't seem to work. The blank cells get highlighted.
View 13 Replies
View Related
Feb 1, 2010
I have a worksheet with conditional formatting applied. My formula works to change the cell color, font color, and strike-through the font if a "yes" answer is indicated. It all works well EXCEPT that the font color and strike-through ONLY apply to the top line on my cell.
Each cell has a title line, and a few paragraphs in it, with separations between the title line and the paragraphs. I kind of like having the title line a different font, but when it's time to strikethrough I want ALL of the text struck through.
View 9 Replies
View Related
Feb 17, 2010
I want to create a conditional formatting based on a cell in a row. If the value of that cell is greater than or equal to $20 the entire row would be highlighted with a color.
the main thing is that the cells in the rows have if formulas that supple the data for the cells. The cells will be blank if nothing is to be displayed but they will still contain the formula.
View 12 Replies
View Related
Feb 22, 2010
I am working on a spreadsheet in Excel 2007 and i am trying to use a formula in the conditional formatting that will highlight a cell if the date is prior to today's date and the next column over is no. the formula i am using right now is =IF(F2>TODAY(),IF(G2="NO",TRUE,)). the problem i am having is that it is highlighting all cells that have a date and the next column over is no.
i can't figure out how to get it to do an if this, then that function correctly. one other thing that could potentially be causing my problem is that the cells.
I am trying to have highlight are calculated themselves based on a formula. see the attached spreadsheet for information.
View 4 Replies
View Related
Jun 4, 2012
I want to format a cell conditional on the value of another cell. I have read numerous threads on the topic and no matter what I do, it doesn't seem to work for me. I've made it a simple as possible by creating a new workbook and entering:
A1: 5
B1: 2
C1: Some random text - this cell has the conditional formula: ="A1>B1"
However, the cell doesn't get formatted. Here is a screenshot and here is the simple workbook.
View 7 Replies
View Related
Dec 12, 2012
If a cell is smaller than the cell above, I want the cell to be formatted.
View 1 Replies
View Related
Feb 15, 2013
I want to color format a cell based on a percentage value in another cell but I just can't get my head around how to solve it. I've attached a file that explains it better.
E.g.
The formatting for A1 should be something like:
If A2 is
> +/- 10% RED
+/- 6-10% YELLOW
+/- 5% GREEN
Attachment 214464
View 6 Replies
View Related
Apr 22, 2013
I have a spreadsheet of stock data open, high, low, close etc., that streams in real time from thompson reuters. i am trying to conditionally format the G column which is the "(HIGH)" price of the day to alert me when the value of that cell changes price. Basically during the first 15 min of trading my software filters down the top performing stocks of the day. During that time i drag and drop the information in the excel program that is linked to the stock quotes program which might give me up to 30 or 40 stocks. The conditional formatting is to alert me after the 15 min mark if the "(HIGH)" value in that column has changed price to a new high. I can control the stream of the data with a button that turns off the data stream so that it only goes live after 15 min.
photo (1).jpg
View 5 Replies
View Related
Jul 11, 2013
I have a table that has dates starting from A6 which is a whole month say 01/2/2013 to 28/02/2013 like a gantt template.
I need if the word "Ordered" is in B5 I need X (crosses) in the date cells starting from B6 to end of month until someone changes B5 to Delivered then I need the X to disappear.
test.xlsm
[URL] ....
View 2 Replies
View Related
Aug 27, 2013
This is the most simplest conditional formatting which is working except on some cells. I want to compare two columns cells ex : if A=B , then format A as green.
Its working, the "formula" and "cell value contains" everything except for few cells. Cell B has formula's in it related to other cells. When i enter values in other cells, Cell B computes but A refuses to format "green".
If I just enter same value[ex 1, 1]in both the cells sans formula ,it highlights A green . I want to know , if the formula in cell B is causing the errors in formatting and if it is
View 2 Replies
View Related
Dec 31, 2013
In conditional formatting, which formula do I use to refer to another cell? Trying
=(ISBLANK(K1))
without luck.
View 9 Replies
View Related
Jan 6, 2014
I am attempting to apply conditional formatting such that cell B3 is green if it is greater than or equal to cell B2, and red if less than cell B2. Then to apply this conditioning across the row of data (i.e. C3 >= C2 green, C3 < C2 red; D3 >= D2 green, D3 < D2 red; E3 >= E2 green, E3 < E2 red ... etc.). Needs to be formatted such that if I change the value in cell B2, the colour of B3 is automatically updated.
View 4 Replies
View Related
Jan 6, 2014
I need a hand with conditional formatting if possible?
In the attached document i need the cell fill for the cells containing values (in column C) to change if data on sheet 2 is anything other than blank.
e.g. Sheet 1 C3 would be green fill and C4 would be no fill as Sheet 2 B3 contains a date and B4 does not
View 4 Replies
View Related
Feb 4, 2014
is possible to construct a conditional formatting scenario and at the same time apply that formatting if the cell entry is within 10% either way of the logical test?
For example if you apply conditional formatting if the cell value is between 300-500 is it possible to add in a 10% swing on each value?
View 3 Replies
View Related