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

Conditional Formatting Based On Another Cell Conditional Format

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

Formatting Cell Currencies Based On A Selection In A Single Cell

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

Conditional Formatting From Another Cell

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

Conditional Formatting In Cell

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

Conditional Formatting - Change Cell Color Based On Date In Another Cell

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

Conditional Formatting - Hide Value From Cell Until It Changes

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

Conditional Formatting Referencing A Second Cell?

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

Conditional Formatting - Highlighting The Value In Particular Cell Red?

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

Conditional Formatting Using Cell Reference?

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

Conditional Formatting On Cell Based On Value

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

Conditional Formatting - Cell Not Being Highlighted

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

Conditional Formatting Based On A Value In A Different Cell?

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

Conditional Formatting When Cell Does Not Equal Another?

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

Conditional Formatting If Cell Contains Comments

Oct 3, 2008

Is it possible to use conditional formatting on a cell if it contains comments.

View 2 Replies View Related

Conditional Formatting To Color Cell

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

Conditional Formatting With Empty Cell

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

Conditional Formatting Only Top Line Of Cell

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

Conditional Formatting Of A Row Based On A Value In A Cell Of That Row

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

Conditional Formatting (highlight A Cell)

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

Conditional Formatting Based On Value Of Another Cell

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

Conditional Formatting Basing On Cell Above

Dec 12, 2012

If a cell is smaller than the cell above, I want the cell to be formatted.

View 1 Replies View Related

Conditional Formatting Based On % In Other Cell

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

Conditional Formatting Value Change Of Same Cell

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

Conditional Formatting Add Text In Different Cell

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

Conditional Formatting With One Cell Having A Formula?

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

Conditional Formatting - Refer To Another Cell

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

Conditional Formatting - One Cell Based On The Value Of Another

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

Conditional Formatting If Cell Value Is Not Blank?

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

Apply Conditional Formatting If The Cell Value Is Between 300-500?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved