Highlight Cell Once Certain Condition Has Been Satisfied?

Jan 22, 2013

I run a small business, and I use Excel to track my books especially receivables.

I have a list of Invoice numbers and Invoice amounts for every customer.

My customers pay periodically (every two weeks, etc), but the tricky part is that the amount paid may not necessarily reflect the exact amount of invoice still being owed. And there may be two, three or five invoices outstanding at any one time.

I have another column which contains payment amount made by customer.

What I would like to do is to highlight the INVOICE NO. cell once the amount of that particular invoice has been bypassed by the total amount paid (i.e. the full amount of that invoice has been paid). Then I can close that invoice.

Here's what my list looks like roughly.

INV NO. - INV AMOUNT - AMOUNT PAID
INV1 ------- 100 ------------- 80
INV2 ------- 250 ------------- 90
INV3 ------- 150 ------------- 80
TOTAL ----- 500 -------------270

View 13 Replies


ADVERTISEMENT

Condition Format: Highlight Whole Row

Nov 27, 2007

i cannot find my old msg , so i re-read again.

let me repeat my question one more time .

right now i can achieve is :-

when i enter in cell $A$6 " win " and i set the conditional format when this cell text = Win. i highlight "RED color "

i can set the above without much problem.

what i want to a bit more is when $A$6 ="Win" i wanted , the Row A6 till F6 cell all highlighted with red.

View 9 Replies View Related

Highlight Certain Cells Given If Loop Condition

Feb 14, 2013

I have a piece of code that basically performs a calculation and prints one value to a worksheet at the end. This code is part of a larger simulation model and by the end of the model this piece of code is executed thousands of times and thousands of values are printed to the worksheet. However, at the start of that piece of code there is an if statement which influences the end result. My question is, how to identify which end values were calculated using each method in the for loop.

For example would it be possible to highlight the cell (in which the value is printed) in red if the

VB:
num= 0

Part of the if statement was used and highlight the cell (in which the value is printed) in green if the

VB:
num= 1

Part of the if statement was used?

This would identify which values were calculated using each method.

VB:
'Change in Model set prior value
If ws1.Range("D" & Rows.count).End(xlUp).Row < 2 Then
num = 1
counter1 = counter1 + 1
sh1.Range("g3").Value = counter1

[Code] .....

View 1 Replies View Related

Finding The Lowest Value And Highlight With Condition?

Feb 3, 2014

question 1 : In the below sheet, is there a way to use the condition highlighting the quantity for each item which has the lowest (prices/pcs) value?

for example : comparing the ITEM 1 -> $8/10 , $6/8, $9/20, $11/20
and highlight the 8pcs for item 1, indicates item 1, seller bill has the best offer.

Question 2 : Since I will have at least 50 items, is there a way to just copy the formula and apple to each row ?

question 3 : I would also like to apply a formula that can calculate the price and quantity of each item. for example : on the I column, for Item 1, i would love to order 3 orders, since bill has the best offer, so I will have 24pcs on column I3. And i would love to apply such formula to each item for each row.

Question 4 : For the cell J3, I would like to know if bill has the best offer for the item 1, order 3 orders, the price for item 1 will be $18. And I would like to apply such formula to each row for each item.

I do not want to create another column for each seller: such as ($/pcs) to find out the best offer, since i will have at least 35suppliers , and try to make the sheet look as nest as possible.

View 1 Replies View Related

Macro To Highlight Rows With Certain Condition

Nov 5, 2012

How to create a macro that would highlight a row that has the following condition :

name

ref num

sam

123456

sam

123456

alice

342333

In this table, i need a macro that would highlight the row that has alice data due to it been having 1 ref number count under the ref num column. The macro should loop and end with msg " there are 'x' records having 1 ref number count"

View 1 Replies View Related

Highlight Cells Meeting Condition

Jun 11, 2007

I have a spread sheet with the following data (starts from A1). Please note that a number of rows may not have the ShipmentValue and/or CustCategory:

OrderDate, CustName, ProdName, ShipmentStatus, ShipmentValue, CustCategory

I am trying to write a code which will highlight (say with Yellow Fill) those rows (Columns A:F) Where the ShipmentStatus = "Late" or where ShipmentValue is missing (i.e. blank). I have been able to get only as far as the code below and would appreciate your help in completing the
Sub Highlight()
' This code will highlight those rows A:F in which the Shipment Status
' (Column D) = "Late" or the ShipmentValue (Column E) is blank

Dim MyRng As Range
Dim StatusChk As String
Dim RowCount As Integer ' Counter to count the # of rows
Dim ColCount As Integer ' Counter to count the # of columns

StatusChkCriteria = "Late"

View 9 Replies View Related

Highlight Rows When Change In Condition Occurs

Apr 11, 2008

I have a whole spreadsheet of data which looks confusing so I need to make it look more attractive to the viewer.

I have column A which shows a number of the branch conducting the transaction and the column will contain numerous different branches which I have sorted into branch order.I think there are too many branches to use the conditional formatting condition (probably 5000 different numbers)

What I wish to do is change the colour of the cell when the branch name changes.

For example
Column A= Branch number
123
123
123
345
345
345
456
456
456
789
789

What I need is to highlight all branches 123 with a colour (any colour say red) but when the change in branch number occurs to 345 use a different colour to highlight all the branches (cells) with the number 345 (say grey).Then when the branch changes to 456 use the same colour that highlighted branch 123 (ie red) .Then again when the branch changes to 789 change the colour back to grey again so the spreadsheet shows rows highlighted when a different branch occurs

The above example would look like
Column A
all branches with 123 ( cell highlighted any colour but say red)
branches 345 cells highlighted grey
branches 456 cells highlighted red
branches 789 grey

View 9 Replies View Related

Checking On 2 AND Functions If One Or Other Is Satisfied

Mar 24, 2014

I am trying to compare 2 conditions to see if one or other is correct and if so out put ok if correct and wrong if incorrect i tried this formula but does not work.

IF(OR(AND(j3="UP",t3=1),(AND(j3="DOWN",t3=-1),"ok","wrong")

View 4 Replies View Related

Sum The Range Of Data If Two Conditions Are Satisfied

Jul 31, 2008

My data is as follows

MonthMilesAmountLocation
June 8£3.20 Loc1
June 8£3.20 Loc1
June 8£3.20 Loc2
July 8£3.20 Loc2
July 8£3.20 Loc1
July 38£15.20Loc1
July 4£1.60 Loc2
Aug 38£15.20Loc2
Aug 4£1.60 Loc1
Aug 8£3.20 Loc1

Here is what I want to do

- Avoide filtering and manually calculating data for the total expense in the month

I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for
(4)LOCATION LOC1

I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???

View 10 Replies View Related

Delete Rows If Criteria Is Satisfied

Apr 25, 2008

if anyone out there had some code that would delete an entire row if a cell contained a value either = or > 0. I have attached a sample of my spreadsheet it contains two worksheets and I need the same task performed on each. The list may differ from day to day in length so I would need to work from the bottom of the worksheet up and I would only need it to work up to and including cell D4. The column that I need to take the action on is D and the heading is MaxDebit2.

View 6 Replies View Related

Cell Equals 0 Changes When Condition Met - Leave Unchanged When Condition False

Mar 24, 2014

I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.

The Cell formula that I am trying to use is as follows:

=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))

A3 contains TODAY()

H114 is the cell being calculated

AA118 is the cell containing the calculated value for the current month.

View 2 Replies View Related

Freezing The Formula After Certain Time / Formula Has Been Satisfied?

Aug 7, 2014

I am trying to create a graph of my data at the end of each month. The graph will show a count of the number of instances of each category in the data.

For example:

A1 =TODAY()B1 =MONTH(A1)

Column A from A3 down = 1,2,3,4,W,H,L
Column I between I2 and I140 = multiple instances of 1,2,3,4,W,H,L

July
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A3)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A4)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A5)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A6)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A7)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A8)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A9)),"0")
=IF($B$1=7,(COUNTIF($I$2:$I$140,$A10)),"0")

My problem is that when it comes around to August or the next month, I want the final count of July to be fixed/frozen so it won't change from the 1st of the month. I can then use the July column's data for my graph.

Can a formula convert itself into a value or freeze itself after certain criteria has been fulfilled?

View 7 Replies View Related

Cell Formatting - Red Text If Cell Matches Certain Condition Based On Another Cell

Sep 24, 2013

I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)

When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.

I need the text in column AQ (the monetary amount) to become red when the the opposite is true.

i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.

View 2 Replies View Related

Conditional Formatting - Highlight Cell If Exists In Any Cell In Range Of Above Row

Apr 14, 2013

This is a conditional formatting problem I have not been able to resolve:

I have a range, say A1:N30

Each cell contains a text/number combination.

I would like to highlight each cell on a specific row if the vale exists within the row above - the issue is that the cell values are not in the same columns.

I have attached a sample workbook with the desired output.

Conditional formatting.xlsx

View 2 Replies View Related

Macro That Will Highlight Cell And Four Rows To Right Based On Text In Cell

Aug 6, 2014

I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.

I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.

It would basically say :

If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).

View 10 Replies View Related

Conditional Format: Highlight A Cell If Any Cell In The Range To The Right Was Greater Than Zero

Mar 28, 2007

If I wanted to highlight a cell if any cell in the range to the right was greater than zero, what formula would i use. I have tried =IF(L1:AD1>0,1) with the result returning for only the cells in column L. Row 3 has no value in column L but a value in column N with no result to highlight the cell.

View 4 Replies View Related

Multi Condition Format? Lookup And Date Condition Valid For

Aug 13, 2009

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 Related

Highlight Cell Based On Value Falling Between Certain Range In Another Cell?

Feb 1, 2012

Let's assume Cell A2, A3, A4, etc, only contain names/text.

Then let's assume I have set up cell B2, B3, B4, etc, to contain a percentage value based on a formula and some other entries I have made.

It should be simple, but I'm having trouble - how do I set up A2, A3, A4, etc, to highlight into different colours based on Column B's data/percentage falling into a certain range?

ie: - if B2 is between 0% - 25%, then highlight A2 green
- if B2 is between 26% - 50%, then highlight A2 orange
- if B2 is between 51% - 100%, then highlight A2 red

View 2 Replies View Related

Selecting Cell A2 Will Highlight/change Font In Cell A1

Jun 18, 2006

How can I change the font color or highlight cell A1, when I select cell A2. Cell A2 is still blank, no value entered yet. I also would like cell A1 to return to original font color or no hightlight when cell A2 is no longer selected (active), whether a value is entered or not in cell A2. Let me clearify, (A1 thru N1) and (A3 thru N3) have diferent dates and the font color is white, as is the cell too. Therefor you can not see the dates in (A1 thru N1) or (A3 thru N3).

However, when any cell (A2 thru N2) or (A4 thru N4) is selected (example A4), the cell above it (cell A3) changes font color or highlights, so the date can be seen in (A3). But when cell (A4) is no longer selected the above cell (A3) hides the date (changes back to white font on white cell). Data or a value is not necessarily entered into (A4) for (A3) to change. (A4) is only selected to show the date in (A3). But if data or value is entered into (A4), the date in (A3) will still be seen until (A4) is no longer selected/active.

View 4 Replies View Related

Disable/enable A Cell Based On A Condition Which Is Dependent On Other Cell Data

Jan 26, 2007

how can we disable/enable a cell based on a condition which is dependent on other cell data

View 4 Replies View Related

Formula To Change Cell Colour :: Shade A Cell Depending On Condition

Feb 9, 2007

Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))

View 3 Replies View Related

Highlight The Cell

Mar 25, 2009

i have got a spredsheet, with clients names on it and in cell Q i have the clients account number, when iam looking at the clients name i also want to see the number, is there any way to highlight the cell i am on.

What i mean isusing the arrows keys i want to make the row highlighted, is there a way to do this

View 2 Replies View Related

How To Highlight Cell Value

May 15, 2014

I have a file in which I Concatenate the cell values like A1, B1, C1 and this vlookup of this concatenated value to the another range where same concatenate is used.

what I want to do is:

highlight the cell which is different in two concatenates.

For ex.
VLOOKUP
CONCATENATE
Value 1
Value 2

Value 3

CONCATENATE
Value 1
Value 2

[code].....

As you can see, in the above example I want to highlight the cell value which is different in two concatenates.

View 7 Replies View Related

Highlight Cell If Contains

Jan 11, 2007

i have a sheet which contains large data.All the cells as formated as TEXT
e.g. value -863.45 appears as 0000-863.45

I have 3 types of values
1. headings => HEAD-1-TEMP-DATA
2. Only '-' sign => -
3. Numeric values with -ve sign => 0000-863.45

i want to hilight on Type3 values ..... is it possible with conditional formatting.
Or any other way w/o changing any value in the original sheet?

View 9 Replies View Related

Highlight A Cell

Sep 18, 2008

Have ABC row and EFGH row and just want to color the cells that only have
double numbers in it....In Conditional formatting----

example---All are in separte rows---

ABC ------EFGH
123-------5544-----55red----44red
332-------4498-----33red---44red
212-------2132-----22-----22

just the doubles I need to color them RED---

View 9 Replies View Related

Looping Down Through Worksheet While Condition Next Cell > Current Cell

Aug 27, 2008

I have 2 workbooks, A (source) and B (destination).
Workbook A has 1 sheet named 'master' and has 10 columns a:j

I would like to copy a range of data in worksheet 'master' for entire 10 columns while the cell value (moving down worksheet) of the next cell in column A is greeater than the current cell. eg. if cell values of range a1:a6 = 1 then cell value a7 =2, then copy range a1:j6 to workbook B in sheet 'r1'.

Then I would like to go back to Workbook A sheet 'master' and begin process again starting from where it left off at a7.

View 9 Replies View Related

Highlight Cell That Is Closest Number To Different Cell?

Apr 4, 2014

[Code].....

I am running a time series linear regression on a credit card company. The left most column is the amount of cards they had on a certain year and the prediction is the cards the regression formula predicted. The + and - are the columns added or subtracted the standard error which is 3.25. I am trying to highlight the closest +/- number to the original cards. For example, in the first Row 76.98 is closer to 78 than 83 is so i would like to have 76.98 highlighted...showing that subtracting the standard error gave us the closest prediction. The second column the 87.085 cell should be highlighted because adding the standard error gave us the closest prediction to 86.4.

View 4 Replies View Related

If Cell Value Is Greater Than Another Cell Value By 2 Years - Highlight Red

Mar 26, 2014

I have a spreadsheet that records "Audit Date" in column C & "Audit Due Date" in column D. "Audit Due Date" is always 2 years from "Audit Date". I think I learnt the correct forumla to use =EDATE(C2,24). What I would like Excel to do is to highlight "Audit Due Date":

Red: If the audit was completed 1.5 years+ Amber: If the audit was completed between 1 & 1.5 years Green: If the audit was completed less than 1 year The colour key will show me which audits need chasing.

View 2 Replies View Related

Auto Highlight Cell Based On Other Cell Value?

Dec 6, 2013

I'm having a difficult time making this work properly.

To simplify, I want:

cell A1 to fill with blue when cell G1=0 or greater.
cell A1 to fill with yellow when G1<0

Next step is to apply this to cells A1:A100 based upon cells G1:G100.

View 3 Replies View Related

Conditional Formatting :: Condition 1 Override Condition 2?

Feb 29, 2008

I'm having trouble with the two formulas working in conjuction with each other.

Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))

Condition 2:
=TODAY()>=A1

"Condition 1" seems to override "Condition 2"? Ever see that?

View 9 Replies View Related







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