Perform Conditional Test Based On Sum Row Of Cells Using VBA?

Feb 6, 2014

In the past I have used VBA script to change column with size based on cell values as per follows:

[Code]....

Now I wish to change row size in a similar fashion only the criteria will be a maths function rather than a string in a single cell. The maths function will simply be a sum of cells in a row. I will need to test whether or not the row ads up to a number greater than zero.

To illustrate what I am trying to achieve I have (poorly) written out a rough attempt at the code below:

[Code] ....

I am unsure how the syntax works for this, I considered defining the range as a string first so I could test the value of a single variable (by defined name) rather than directly referencing the row of cells in the formula. As this code needs to be repeated many time for many rows the simplest solution would be b.

View 7 Replies


ADVERTISEMENT

Excel 2010 :: Perform Sum Based On Other Cells Content?

Jan 31, 2013

Exel 2010 cells formatted to Time

need to look at h1 and h2 if no data ignore

other wise add data together if total exceeds 08:45

then deduct 45min from h1

if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only

Example

h1= 5:00
h2= 2:00
h3= 8:00

h1=
h2=
h3=

h1= 8:00
h2= 2:00
h3= 7:15

View 3 Replies View Related

Test Two Cells And Set A Third Cell With Different Values Depending On Test Results

Aug 21, 2013

I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.

A1 represent a number of years and B1 represent gains or losses (negative)in dolars.

The way I see the logic is as follows:

If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)

If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.

On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.

I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:

If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL

View 3 Replies View Related

Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met

Oct 17, 2007

I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard

View 2 Replies View Related

How To Perform 2 Consecutive Conditional Formulas For Same Row

Dec 21, 2013

How to set some conditions for identifying trends. there are two trends I am trying to track, one if there are any 3 consecutive months where the score was >0, and the other was if there two occurrences of 2 consecutive months (tracking area 1 has that example), tracking area 2 shows the three months in a row, and tracking area 3 did not meet either. It doesn't mater if meets both in same row I only need to identify if yes or no.

View 3 Replies View Related

Converting Cell Values To TEXT For Conditional Test

Apr 28, 2006

I have a macro that takes a value of one cell ("Cells(iFoundPass, 5")) and makes another cell (Admin_Level) equal it (the valules can be wither 'Administartor' or 'Standard'). When someone runs a macro it checks to see if "Admin_Level" equals "Administrator". The problem is that when that first macro runs, even if ("Cells(iFoundPass, 5")= "Administartor" and then "Admin_Level" will be made "Administrator" , my IF/THEN statements do not work. I think this is because the value of "Admin_Level"("Administrator" in the example above) is not text. I can't seem to figure out why. I do know, though, if I manually enter in 'Administrator' in the cell name "Admin_Level" then my IF/THEN works.

Sheets("Config").Range("Admin_Level") = Sheets("Config").Cells(iFoundPass, 5)

and then this is the logical check that runs anytime someone wants to run a macro...

If wb.Worksheets("Config").Range("Admin_Level") <> "Administrator" Then
PasswordForm.Show
End If

View 2 Replies View Related

Perform Action Based On Choice From Validation List

Sep 22, 2009

In all the cells in column F I have a validation list with several predefined choices, but at the bottom of this list I want an "Other..." option, which when chosen, enables the user to enter a new string in that cell if none of the predefined did not match.

View 7 Replies View Related

Excel 2007 :: Conditional Formatting Empty Cells Based On Full Cells?

Nov 17, 2011

Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A

Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).

there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.

View 5 Replies View Related

Conditional Or IF - Formatting Another Cells Font Based On Different Cells Text

May 3, 2013

What I am looking to do is;

If cell A2 = "Closed"

then I want cells B2 and C2 to strike through its own text.

I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah

but if

A2 = Closed
then B2 and C2 = Blah blah blah

View 5 Replies View Related

Compare Cells & If Equal Perform Function

Jan 25, 2008

I need to vba help on the following:
For 1st A1 to Lastrow
{
if cells are equal
{
F1 = (C1+E1) - (B1+D1)
}
}
below is the records that i'm trying to worked on..has about couple thousands rows...
Description Debit Credit Debit Credit Profit
74HBHZMPXC 3,321.00
74HBHZMPXC 3,321.00
74HBHZMPXC 10.00 10.00
75HBCTIWSI 30.55 30.55
75HBJWFZEU 1,095.60
75HBJWFZEU 1,095.60
75HBJWFZEU 2,198.00 8.00
76HA25FYGB 925.00
76HA25FYGB 925.00
76HA25FYGB 1,856.00
76HA25FYGB 1,856.00
76HA25FYGB 1,856.00 6.00

View 9 Replies View Related

VBA Macro To Select Cells With Certain Value And Perform Action On Adjacent Cell

Mar 10, 2014

Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.

Attached File : LoopingVBA.xlsx‎

View 3 Replies View Related

Conditional Based On Other Cells

Mar 11, 2008

what would I do if I want cell B:1 to be formatted a certain way if the value in A:1 is a certain value?

View 9 Replies View Related

Conditional Formatting Based On Another Cells Value?

Aug 18, 2014

On the attached worksheet I'm looking for the squares in F4:F6 to be coloured corresponding to the colour names in D4:D6 using conditional formatting. I just can't get it to work today.

View 1 Replies View Related

Conditional Formatting Based On Cells?

Mar 30, 2014

I have a worksheet which has the years 2001 to 2013 on in row1. I am trying to find a way of highlighting the entire row in green if a specific year contains an "a" (Marlett, tick). The complex part is that I would like the specific year to be determined by an entry input into cell A2 on sheet 2. See Attachment for reference.

View 2 Replies View Related

Conditional Formate VBA Based On Other Cells

Jul 17, 2007

I am trying to convert the following macro to a Worksheet (section change) format, but cannot figure out how to have the cells B87:M150 change based upon the text (red, green, white, amber, na) in O87:o150.

Sub ordinate()
Dim r As Range
For Each r In Range("o87:o150")

If r.Value = "RED" Then
r.EntireRow.Interior.ColorIndex = 3

ElseIf r.Value = "AMBER" Then
r.EntireRow.Interior.ColorIndex = 44

ElseIf r.Value = "WHITE" Then
r.EntireRow.Interior.ColorIndex = 2

ElseIf r.Value = "GREEN" Then
r.EntireRow.Interior.ColorIndex = 4

ElseIf r.Value = "NONE" Then
r.EntireRow.Interior.ColorIndex = 2

ElseIf r.Value = "NA" Then
r.EntireRow.Interior.ColorIndex = 15
End If
Next

Range("N86:IV132").Interior.ColorIndex = 2
Range("A86:A132").Interior.ColorIndex = 2
End Sub

View 9 Replies View Related

Creating Dynamic Range Based On Test?

Feb 14, 2013

I need to do stats for each month within a set of data. Ie take the min for all data in the month of Jan only and repeat for all other months etc. Is there some way I can set up a Min function to only consider the data belonging to jan for example and have it change and only consider Feb, then march.. etc Seems like it would be easy to just do this manually but there is a large timeframe over which data was collected so would take forever.

Have attached sample of data. rainfall.xlsx‎

View 4 Replies View Related

Test 2 Cells Before Using SUM

Nov 28, 2007

I am using the formula below to return a blank cell if either logical test returns true but apparently the OR operator returns a #VALUE error when applied to a non numeric value in a cell - in this case cell E40. =IF(OR(E40="N",P40=""),"", SUM(P40,S40,V40,Y40,AB40,AE4))+F40. My goal is to return a blank cell if E40 contains the letter N or P40 is blank, otherwise perform SUM(P40,S40,V40,Y40,AB40,AE4))+F40.

View 4 Replies View Related

Conditional Format Formula Based On Two Cells.

Apr 9, 2009

I want to create a conditional format formula which is based on values in 2 cells.

Example:

Cell B1 = 100
Cell C1 = 50

I want a formule for cell A1 which turns red for exmaple when B1 is higher then 85 and Cell C1 is lower then 85. Only in this case the conditional format should work.

View 8 Replies View Related

Adding Cells Based On Conditional Formatting

Sep 17, 2009

I am using conditional formatting to make cell background's go red if the data satisfys some criteria

Now I want to add the values in those red cells, can anyone make a suggestion as to how I can do this.

View 6 Replies View Related

How To Conditional Format Based On Adjacent Cells

Oct 18, 2013

Picture1.jpg

How do I create a CF if a cell is blank (in my case represent no sales) i want to have it yellow (ex. C2)

But if it has two consecutive blank cell i want to CF with Red. (Ex. F3)

But i also need to exclude blank cells when we we have no previos sales of that item (Ex.B3).

View 3 Replies View Related

Conditional Formatting Based On Sum Of Pairs Of Cells

Nov 14, 2013

I have created a table that has working hours of staff members over many weeks. Week number as column headings (1 to 52) and staff name as Row headings. E.g a row may be

John Smith, 37, 37, 37, 37, 64 (commas to show seperate cells)

How would I go about using conditional formatting so that the formatting changes according to the sum of the values in each pair of cells?

I need to add the total hours of every two weeks for some staff and change the fill colour of both cells accordingly to highlight which weeks staff have worked too many/few hours.

So (B1+C1) would be a pair, the total would decide which fill colour is used on both B1 and C1, and then (D1+E1) would be the next pair and so on.

I have tried using 'a formula to determine which cells to format' and placing =(B1 + C1) = 74 and making it fill the cells green but this appears to be doing (B1+C1) as the first pair and then (C1+D1) as the second and changing the format for the first cell only.

View 7 Replies View Related

Conditional Formatting Based On Another Cells Contents?

Sep 19, 2012

I'd like to format (colour fill) C1 when H1 contains the word "Bills"

I've tried to "use a formula to determine which cells to format" but it keeps using absolute references ($H$1).

Ideally I'd like to apply the "format" to the entire column C.

I did think I'd be able to use OFFSET but it's not working.

View 1 Replies View Related

Format Cells Based On Conditional Count

Feb 15, 2010

I have a drawing register that contains drawing reference numbers in column D. When a new revision of a drawing is issued it is added to the bottom of the list. What I would like to do is any previous revisions to be greyed out and struck through.

The conditional formatting
= COUNTIF($D$1:$D$100,D1)>1
works well but strike through all duplicates, I need the most up to date left without the format until it is superceeded

View 2 Replies View Related

Conditional Formatting - Based On Another Cells Contents

Apr 27, 2007

I have a worksheet that I've applied conditional formatting to which works very nicely apart from when I use the formatting and apply it to a cell based on the contents of another.

What I don't understand is when I put a formula in cell b1 to read =$a1="yes" and format accordingly it works. So when I type in "YES" in to cell a1 ,cell b1 is highlighted. When I type in "NO" the B1 cell isn't highlighted which is what i would expect. IF however I delete what ever is in cell A1 the cell B1 is still highlighted.

View 8 Replies View Related

Copy Test From Cells Into Row

Jul 9, 2014

I am trying to find a script where I can see information more clearly. I am trying to copy certain cells and have them into a single row. For example, everytime the word USER comes up, I want that in a single row and all the information that comes after it and below to be in the same row. So I would want all this information in a single row. And it would start a new row when the word USER comes up. I will attach an excel table for an example. Working progess.xlsm

1READY
LU*
USER=irrcertaNAME=CERTAUTHAnchorOWNER=irrcertaCREATED=01.097
DEFAULT-GROUP=PASSDATE=00.000PASS-INTERVAL=N/APHRASEDATE=N/A
ATTRIBUTES=REVOKED
REVOKEDATE=NONERESUMEDATE=NONE
LAST-ACCESS=UNKNOWN
CLASSAUTHORIZATIONS=NONE
NO-INSTALLATION-DATA

[Code]...

View 1 Replies View Related

Conditional Formatting Based On Difference Value Between Figures In Two Cells?

Apr 10, 2014

I would like to use conditional formatting to mark cells with arrows depending on the difference between the values in those cells and figures from different cells. Namely, I have percentage values in column A and B. I need cells in column B to be marked with arrows (up, right or down) when the difference between value B and A is bigger than 2%, bigger than 0 but smaller than 2%, and smaller than 0 respectively (see the spreadsheet attached).

View 2 Replies View Related

Conditional Formatting Formula Based On Text In Two Cells?

Jan 8, 2014

I need a formula for conditional formatting which highlights a row if A has the text "Ex" and B has "Paid".

View 2 Replies View Related

Conditional Formatting Based On Top 10 Values In Adjacent Cells?

Jun 3, 2014

I have a matrix in excel to show % and $ for multiple columns - set up like this:

Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3

the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.

View 6 Replies View Related

Using Conditional Formatting To Fill Certain Cells Based On Variable?

May 23, 2013

The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.

View 2 Replies View Related

UserForm_Initialize Code To Test Certain Cells

May 9, 2007

The below code tests a particular cell before displaying the userform. If Range ("Bal_BF") contains an error (such as "#N/a"), a message box is displayed and the form should NOT display.

However, the code is still trying to display the userform, and gives me a error message "Object variable or With Block variable not set".

When Bal_BF contains a value, the form displays correctly.

Private Sub UserForm_Initialize()

If Employee = "" Then Call Retrieve

If IsError(Range("Bal_BF")) Then
Call MsgBox("Your name doesn't appear in the Holiday & Absence file." & vbCr & vbCr & _
"Please see Leila Fretton or Neil Jimack about this.",.........

View 9 Replies View Related







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