Evaluate Conditional Formatting Result

Sep 28, 2007

I'm having trouble figuring out how to evaluate the result of Conditional Formatting using VBA.

I've got a series of cells ( dates) that are formatted in BOLD when a corresponding cell elsewhere in my spreadsheet shows that the date is Actual and not Projected ("A" vs. "P").

The Conditional Formatting works fine, I just can't figure out how to get VBA to skip the cell if the value is not BOLD based on the Conditional Formatting result.

ex:

For Each i In Selection
If i.FormatConditions(1).Formula1 = True Then
'OR i.FormatConditions(1).Font.Bold = True Then
i.Copy
i.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
End If
Next i

View 6 Replies


ADVERTISEMENT

Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell

Aug 3, 2009

I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem:

Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it.

i.e. (in reference to attachment - sheet DATA)
IF E6 = Holiday, then D6 = Red
IF E6 = Half Day Holiday, then D6 = Red
IF E6 = Sick, then D6 = Blue
IF E6 = Sick Half Day, then D6 = Blue
IF E6 = Bank Holiday, then D6 = Green
IF E6 = Compassionate Leave, then D6 = Pink
IF E6 = Unpaid Leave then, D6 = Yellow
IF E6 = Unpaid Leave Half Day then, D6 = Yellow

However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).

View 4 Replies View Related

Excel 2010 :: VBA To Apply Cell Protection Based On Conditional Formatting Result

Jan 27, 2012

I am trying to use the status of Conditional Formatting to toggle on/off protection for a cell.

I have a cell with Conditional Formatting applied if the result of a formula is true. If the Conditional Formatting is applied, I want the cell protection turned on so a user can not change the entry in the cell. If Conditional Formatting is not applied (false), cell protection is turned off and the user may edit.

I am using Excel 2010 and Vista.

View 1 Replies View Related

Conditional SumProduct To Evaluate Iteratively

Sep 18, 2009

I have two tables, one summary sheet which contains a list of 30 projects. The second sheet has the projects broken down by task and status ("on time" or "late"), so it has three columns (project, task and status). I am trying to add up the number of projects within each task that are either "on time" or "late" and put these in two columns, so that the sum of each together will always equal 30.

The trouble is that any given task can appear multiple times for a project, and if one instance of that task is late, I want to count the whole project late for that task. Here is a table of what I am trying to do:

Project Task Status
A1 MU On time
A1 MU Late
A1 FA On time
B2 FA On time
B2 MU On time
B2 MU On time

The desired result of this scenario is that under task "MU" I would show a count of 1 projects on time (B2), and one that is late (A1). For task "FA" I would show a count of 2 projects "on time" (A1 and B2) and none for "late"

I think that an array formula is needed where it assigns a 1 to a project and task that is on time, and a 0 if it is not, and then multiplies these figures for each project and adds them up.

View 9 Replies View Related

Conditional Formatting - Two Different Fields Affect One Cells Formatting For Date?

Sep 16, 2013

A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.

dust 1.xlsx

View 2 Replies View Related

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

IF Statement Using Formatting Criteria (NOT Conditional Formatting)

May 5, 2009

Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:

if(A1=blue background,"Yes","No")

or

if(A1=red text,"Yes","No")

etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.

View 5 Replies View Related

VBA To Convert Conditional Formatting To Fixed Formatting?

Jul 5, 2013

Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...

View 9 Replies View Related

Return Result & Formatting From Lookup

Sep 16, 2009

I have a production planning spreadsheet that is updated every week from a database to a new sheet. This sheet is 'Sheet1', last weeks update becomes 'Sheet2'. Every day people annotate and colour cells for various orders, depending on their place within the planning scheme. It's really only the formats that I need to copy across, I could use the VLOOKUP function for the values - So I suppose you could say I'm looking for a VLOOKUP macro/VBA Code which keeps the original formatting (cell colour etc.)

View 2 Replies View Related

Result Conditional Upon Preceding Values

Mar 19, 2007

I am using the following formula (which I need to adapt) to enter a value in a cell C30.
C30=IF(A30>0,A30,0)
I will need to enter this formaula in column C rows 1 to 300.

The problem is that, in addition to A30 needing to be >0, I only need to enter a value in C30 if a value (>0) is found in column B (B29 going back to B1) before a value is found in the A column (A29 going A1). If a value in the A column is found first then I need to enter "0".

For example, if A30 is >0 and the previous A row A29 is >0, I want to enter 0 in C30.

Another example, If A rows A20 to A29 are 0 and B19 >0, then I want to enter A30 in C30.

View 9 Replies View Related

Change Cells Formatting When Formula Result Changes

Jan 25, 2008

I am having some issues with my conditional formatting code. I need 5 condtions, so I cannot use the built in Cond. Format. Column E is the average of cells F,G,H and when I update the numbers in F/G/H the background color is changed to the appropriate color, but column E does not change color!!! Example: The average (E) will start as a 90 and have a red background, then when the inputs(F/G/H) are changed and the average(E) now equals 30 but it is still shaded red! Is there anyway to have every cell updated, to make the cell change from Red to Green automatically??? Below is a copy of my code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
CellVal = Target
Set WatchRange = Range("E6:H15")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal

Case Empty
Target.Interior.ColorIndex = xlColorIndexNone
Case 0 To 19
Target.Interior.ColorIndex = 8
Case 20 To 39 ..........................

View 4 Replies View Related

Conditional Format Based On Formula Result Being A Whole Number

Dec 6, 2012

I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.

I don't want to include the formula in the sheet itself, just have that as the formula in the condition.

The formula will be along the lines of:

=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)

My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.

View 4 Replies View Related

Multiplying By 0 But Keeping Existing Result - Conditional Multiply

Nov 10, 2008

I know when you multiple anything by 0 the answer is 0.
But I have a list of numbers I need to multiply out
but want to keep the total even if there is a 0 in the sequence
Eg
A1*A2*A3*A4
10*15*0*11
5*21*6*0
0*14*10*0


So A1*A2=150 if I then multiply by A3 I get 0, but I need to ignore the cells that have 0.
So for the last example my answer would be 140.

View 9 Replies View Related

Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?

Feb 9, 2009

I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View 2 Replies View Related

Conditional Formatting - More Than 3

Dec 9, 2008

In a column, there are 4 option for each cell -
"PAYMENT DUE"
"PAID"
"UNDERPAID BY..."
'OVERPAID BY..."

These are filled in by a formula

I would like to make them 4 different colours but excel only allows 3 options

View 14 Replies View Related

If Then Or Conditional Formatting??

Mar 5, 2009

I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?

View 2 Replies View Related

Conditional Formatting With Vba...?

Dec 23, 2009

I seem to have a problem using conditional formatting with Vba in Excel 2003
When I run -

View 3 Replies View Related

Conditional Formatting As $D8 And The RC4 As $C8 In VBA

Nov 11, 2009

Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?

View 2 Replies View Related

Conditional Formatting More Than Once

Feb 3, 2010

I have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"

In "book2" where I am getting stuck is as follows,

Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??

What I need is to repeat the conditional format over and over again forever is there any code for this I can use?

View 9 Replies View Related

Conditional Formatting A Row

Oct 31, 2008

I have used conditional formatting various ways to format an individual cell. I was wondering does anyone know if it is possible to conditionally format a row? Eaxmple: If D7=Manager, then format A7 through Z7. If so, how would I go about setting that up?

View 3 Replies View Related

If/then/else Or Conditional Formatting?

Dec 15, 2009

I'm wanting to create a formula in column B that will return a value of 10 if column A is any value between 70% -100%, will return a value of 5 if column A is any value between 50% - 69%, and will return a value of 1 if column A is any value between 1% - 49%. Can you help me?

View 6 Replies View Related

If And Conditional Formatting?

Oct 29, 2013

I want to do an if (I think I do??) command so that if a1=b1, then the cell turns blue and if a1=0, the cell turns red, with the false statement just being blank.

View 3 Replies View Related

Conditional Formatting In VB

Apr 21, 2014

I have data in column B.

And if the below mentioned values are present in column "B" then the cell colour should change to "RED Colour"

Bombay
Mumbai
Bangalore

if any other values other than the above mentioned then the cell format should remain as is.

View 1 Replies View Related

Conditional Formatting To Get Rid Of #DIV/0!

Jun 11, 2008

I have my formulas in place and they are working. The reason why some are coming up with #DIV/0 is because I do not have the data to complete the calculation.

Is there a way to make the #DIV/0 error go away until I imput the data to complete the calculation? I was thinking there might be a way to do it with conditional formatting.

View 9 Replies View Related

Conditional Formatting Through VBA

Jun 30, 2008

Sub Macro1()
With Range("A1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1"
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=A1B1"
.FormatConditions(2).Interior.ColorIndex = 50
.AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault
End With
End Sub

If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5

Condition 1 is
=IT65533=IU65533
Condition 2 is
=IT65533IU65533

But if I run the code with A1 as my active cell, it gets entered correctly as
Condition 1
=A1=B1
Condition 2
=A1B1

View 9 Replies View Related

Set Conditional Formatting Via VBA

Jul 18, 2008

I would like to add conditional formatting for the whole row based on whether the value of cells in column A is "CON".

For Each c In r
If c.Value = "CON" Then
c.EntireRow.FormatConditions.Delete
c.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:=c.Address = "OK"
c.EntireRow.FormatConditions(1).Interior.ColorIndex = 35
end if
next c
the "CON" is a formula and can later take the value "OK".

The conditional formatting should highlight the whole row as green when that happens.

View 9 Replies View Related

Conditional Formatting / VBA

Feb 25, 2009

I am not sure if this requires conditional formatting/vba function

If I enter something in cell A1 and leave blank in cell c1, in cell c1 it should print the message "Required Entry, Please Fill up". and this condition should be applied to entire column, where ever in column A there is some values and column c is empty this condition should apply.

View 9 Replies View Related

Conditional Formatting

Apr 27, 2006

I don’t know if it is possible in Excel 2000 but I have a simple IF formula in a cell that gives "" on certain conditions.The problem I have is that in another cell I apply conditional formatting – green >100%, red<100% e.g.

Is it possible to have a third conditional format that gives a blank if the cell is ""?

At the moment it just turns out green as it doesn’t seem to recognise the cell as actually been blank as it has a formula within it.I have tried ISBLANK etc.

View 3 Replies View Related

Conditional Formatting > 3

Jun 30, 2006

Conditional Formatting > 3

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim cr As Date
cr = Today()
If Not Intersect(Target, Range("C5:M44")) Is Nothing Then
Select Case Target
Case "X"
icolor = 10
Case "N/A"
icolor = 2
Case cr.Value >= Today()
icolor = 6
Case cr.Value <= Today()
icolor = 3
Case "DATE?"
icolor = 8
Case Else
icolor = 2
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub

View 7 Replies View Related

Conditional Formatting With Vba

Oct 12, 2006

I've written a macro that copies a range of cells (size varies) from Sheet1 to Sheet2, deletes the conditional formatting already there, and applies fresh conditional formatting to Sheet2 on the new range. But, the expression in the FormatConditions is way different than what I specified in the code. Here's my Conditional Formatting

Set mt1 = tData.Columns(1). Find(What:="", After:=tData.Range("A1"), LookIn:=xlValues)
tData.Range("A2:A" & mt1.Row - 1).FormatConditions.Delete
With tData.Range("A2:A3" & mt1.Row - 1).FormatConditions _
.Add(xlExpression, , "=A2=A1")
.Font.ColorIndex = 2
End With

When I look in cell A2, I see this for the expression: "=A65301=A65300". Any idea why it isn't starting with "=A2=A1" as I specified in the code?

View 7 Replies View Related







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