Format Range When New Rows Added

Jul 24, 2009

I have a worksheet on which users will enter data one row at a time, in columns A-K. When the user starts to enter data in a new row #, that is, when they make any column in the next, unused row have non-blank value, I'd like the sheet to update the borders of the row for columns A-K. I want the Range from A1 to K# to have full borders, so that the whole data set is outlined and easier to read.

For a bonus, I'd like the formulas from the previous H, I, and J cells copied down into H#, I#, and J#, adjusted appropriately for their reference changes.

Normally, I would work around the edges of such a VB problem by recording the actions manually and then modifying that code as I learned more. But I'm not sure if what I've done is the right way to start that process. I think that if I use the CurrentRegion property, and the Worksheet_Change event, I'll be well on my way.

ADVERTISEMENT

Updating Named Range When New Contact Is Added Using R1C1 Format With A Variable

Feb 1, 2010

The code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.

I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.

View 2 Replies View Related

Sum Do Not Include Rows Added After The Predefined Sum Range

Apr 16, 2008

I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.

What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.

View 9 Replies View Related

Macro Runs Fast When Rows Added / Deleted Slow When No Rows Added / Deleted

Jun 27, 2013

We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.

Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.

The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.

The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.

We can't figure out why the macro takes longer to run when no changes have been made?

View 8 Replies View Related

Adjust Formula Range Reference As Columns/Rows Added

Apr 4, 2008

I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:

=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998

becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998

View 3 Replies View Related

VBA: Sort And Format A Worksheet After New Row Added

Apr 29, 2009

From the sample file, I am using the data on (start) worksheet. (Sort and format sheet) . This one is way over my head. After new data is added to the sheet via another UserForm, I want to sort the list by name and have alternating backgrounds based on grouped unique names.

View 2 Replies View Related

Format Cell Value Added To Text

Dec 10, 2006

Cell b6 has been formatted to a percentage with 2 decimal places. Using the formula: ="todays percentage is "&B6&", thank you.". The value returned is: todays percentage is 0.78125 thank you. My question is can the returned value from cell b6 be formatted to a percentage or at least be formatted to 2 decimal places? I hope i posted this correctly i am a first timer.

View 2 Replies View Related

Format Cell To Display Number With X Added 0s

Oct 23, 2012

Is it possible to format a cell so that it displays 6x 0's after the number?

I.e. If cell A1 contains the number 14, id like it to display 14000000

However, that 14 million is a monetary value, so I would want it to also display $ , & .

If I change the number the following week to 21, it automatically becomes $21,000,000.00

Ive tried formatting it as a custom, but that only seems to had 6x 0's before the number

View 2 Replies View Related

Format & Find Date Added To TextBox

Jan 14, 2009

I have a log that I am trying to make compatible for international users. They enter bike rides via a form. When the user hits submit Excel finds the date and then posts the ride info.

The problem is that if I format the date textbox (textbox1) as international the date inputted in textbox1 isn't being found in the column.

Here are the snippets of code from the form (other parts of the code were excluded because they shouldn't effect this issue.

Code when the form initializes ...

View 3 Replies View Related

Conditional Format Over Range Of Rows

Feb 25, 2008

I've been trying to get around the 3 rule limit for conditional formatting,
and I've found the code I need on the site

[url]

I've changed it to suit my needs

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A2000")) Is Nothing Then
Select Case Target
Case Is = "Test"
icolor = 6
Case Is = "Test2"
icolor = 12
Case Is = "Test3"
icolor = 7
Case Is = "Test4"
icolor = 53
Case Is = "Test5"
icolor = 15
Case Is = "Test6"
icolor = 42
Case Else
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

This works great for one cell, but I need to format a row range based on the single cell.
So if Test is entered into A1, then it changes to yellow, I also need the next 6 cells on the row to change too- In this case [B1:G1].

But it needs to cover the range in the code A1:A2000

View 9 Replies View Related

Add Formula To Added Rows

Oct 23, 2006

How do I apply a formula to every cell in a given column?

I'd like my workbook to store this formula:
=c<current_row> + d<current_row>
in every cell of column P as a default.

This means that even if I do "insert row", I wouldn't have to copy this formula into the new cell.

View 6 Replies View Related

SUM Account For Added Rows

Jan 4, 2007

I've created a simple budget sheet outlining various group expenses eg: Fixed, Flexible, Variable, and their totals sums.

How can I add all totals while still allowing for a new row to be inserted in any group?

eg:..............Row E

1........Fixed Expense group
2..................$20.00
3..................$40.00
4..................$40.00
5....Total sum $100.00
6.......Flexible Expenses group
7...................$10.00
8...................$20.00
9...................$20.00
10..................$10.00
11..................$50.00
12...Total sum $110.00
13......Variable expenses group
14..................$10.00
15..................$10.00
16..................$10.00
17..................$20.00
18..................$10.00
19....Total sum $60.00
20--------------------
21..Grand Total $270.00

View 4 Replies View Related

Format A Range Of Cells, Columns And Rows Will Vary In Length

Jun 16, 2009

I created a macro on an Excel spreadsheet:

Sub Macro1()

Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57

End Sub

When the macro is run, it works with the exact amount of data that I've entered.

However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)

How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?

View 9 Replies View Related

Moving Rows When New Information Is Added

May 18, 2012

I have a workbook where on one sheet various peoples information is listed and based on their organization the information is then linked to one of a few different worksheets for costs. On the cost worksheets, when a new person is added to the original worksheet, their information populates the next row. At the end of the cost columns I have a the total cost of each person added together. What I need to do is have these totals continue to move down the worksheet as new people are added. Is there a way to do this in excel using VBA or another function?

View 1 Replies View Related

Formatting New Rows Added Automatically

Dec 10, 2009

I am working on a spreadhseet with multiple tables present on the sheet. Each table has various formatting, merged cells, formulas, and defined lists. I need to have the table add a new row below the last including cell formatting, formulas, defined lists, so I can just keep going through and do data entry. The code adds a row at the end so I don't run into the table below the present one, by monitoring how far away it is from the "Type" cell tag in column A. The code below is a compilation from viewing other threads, but it just does a copy and paste, including the data that was input so I'm looking at a duplicate row. How can I get ito to clear values in the new row? I can't seem to get it to paste xlPasteFormat and xlPasteFormulas
Is the new row in fact being added below the last? I think it's up one after the macro had run through.

Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
c = Target.Column
If c <> 1 Then Exit Sub
Application.EnableEvents = False
NextLineValue = Cells(r + 3, c)
If NextLineValue = "Type" Then
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 0).EntireRow.Copy
ActiveCell.Offset(0, 0).EntireRow.PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Application.EnableEvents = True

End Sub

View 5 Replies View Related

Extend Formula When Rows Added

Jan 5, 2007

I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).

I have a column ( Total Hours) that I use to total the new numbers in the row.

If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))

How do I (Can I) get the formula to cascade into the new row.

View 8 Replies View Related

Unchanging Sheet Reference When Rows Are Added

Oct 28, 2009

I have a sheet titled PartNumbers....which contains in numerical order just 1 column of random 1, 2, or 3 character numerical part numbers. nothing special. and not necessarily in 1 by 1 order. like 45,46,47,48,49, it will skip number more like 45, 49, 50, 55. I have another sheet titled FinalReport.....that takes the raw data i have from the PartNumbers sheet and outputs the data onto a nice pretty looking designed sheet.

on 1 column of FinalReport I have =PartNumber!C2, C3, C4 and so on for the total number of parts i have to fill in the FinalReport sheet for each cell. The Question is, on PartNumbers when I want to add a part, say my sheet goes number 45 then 49 and i want 46... What I do is right click row number and select Insert, that adds a new row for my new part number. Now that move doesn't correspond to FinalReport now, it just removes the row i inserted. How do I get everything to flow to FinalReport?

View 5 Replies View Related

Macro That Will Calculate Sum Of Cells Even If Rows Are Added

Feb 12, 2014

So I'm trying to create a macro that will simplify dealing with a file we receive at work pretty often. I've enclosed a spreadsheet that shows a very basic example of the files we receive. What I need to happen is this: On the rows that have only the Company # in column A and Total $ in column D (ie. rows 5 and 6), I need the Total $ amount moved up one row and then for the row it was previously in to be deleted (at that point that row should only have the Company $ and no other data in it so it's basically useless). The problem I'm having is that if any rows are added to the file (for instance if an row was added between rows four and five) it would throw off my macro.

View 3 Replies View Related

Automatically Adding Rows When Worksheets Are Added

Jun 30, 2014

I'm using a workbook that has one sheet that pulls data from all the others, displaying a series of rows that summarizes data from each worksheet, each of which is full of data. Its a worksheet that a lot of people use so its kind of tedious to use as of now, because whenever you add a new worksheet of data you have to manually create a row and then manually change all the worksheet references. Is there a way to make rows automatically add and update when a new worksheet is added? I know it would probably have involve some sort of Macro or VBA.

View 1 Replies View Related

VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

View 4 Replies View Related

How To Automatically Update A Macro To Adjust For Rows Being Added

Mar 5, 2013

I have a worksheet used for a car game. Each row is designated to show one particular car that can be used in the game, so let's say that there are 40 cars taking up 40 rows.

Column A shows the car type and model; column B shows the engine that the cars has represented by a number (the higher the number the better the engine is); and so it continues, Column C shows the transmission; column D the brakes etc.

Underneath the 40 rows (lets say from row 45 to 65) I then have a table with spare-parts that can be used to replace the cars existing parts. This Parts-table is also arranged with engines in column B, transmissions in column C, brakes in column D etc...

This point of this layout is that it should be easy to compare each type of part in the spare-parts table with the corresponding type of part already used in any of the cars as everything is perfectly lined up by the columns.

About the spare-parts table: because the inventory in the spare-parts table often are added to or subtracted from, as parts are won in races or being put on the cars, I have created a macro to sort the parts in each column so as to put the best parts at the top of the table and the bad ones towards the bottom - so far so good.

The problem is this - as I add new cars, the table of spare-parts is pushed down worksheet and that means that the macro no longer will reference the right rows. So the question is this, how do I automatically update the macro to adjust for rows being added above it? I am not really interested in redesigning the worksheet.

View 2 Replies View Related

RowSource To Reflect Data Added To Named Range

Nov 8, 2006

I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?

View 2 Replies View Related

Summary Sheet Calculate Results Change If Rows Added/deleted In Detail Spreadsheet

Feb 27, 2009

[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.

I'm using dynamic name ranges, as follows, for the detail:

WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)

Other detail data is defined as these examples show:

GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)

All detail data begins in Row 6 in the detail spreadsheet.

In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.

Here are two examples of the calcs I'm using in the summary:

=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")

Can someone tell me what is going on? What I'm doing wrong?

View 8 Replies View Related

Use A Named Range To Many Columns Are Added Or Deleted The Data Is Pulled Correctly

May 4, 2006

I have a spread sheet that I pull data from different columns on a particular row. The problem is the code I used works great as long as the column never moves from its current location. Is there a way to use a named range to make the following piece of code work, so no matter how many columns are added or deleted the data is pulled correctly?

View 2 Replies View Related

Application.inputbox: Range To Copy And Paste The Range's Link And Format To A Different Sheet

Oct 7, 2009

Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:

View 2 Replies View Related

Format Range Based On Another Range ...

Apr 9, 2009

Here is my code now. It loops through col E and formats corresponding rows in accordance with the E value:

View 7 Replies View Related

Add Empty Rows And Then Format

May 3, 2012

My frist row of data start at 4:4 but when I run the macro it add a line in underneath this which I would prefer it didn't, again due to my lack of knowledge I placed the following at the end of the macro to hide this row

'ActiveSheet.Rows("5:5").Hidden = True',

I would prefer if this row was not created??

After the macro runs (or during) I would like to have the format changing so the border does not show up between the cells, I would just like an outside broader from a:z columns.

Public Sub My_Insert()
Application.ScreenUpdating = False
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

[Code] ........

View 2 Replies View Related

Format Rows According To The Value Of A Column

Aug 23, 2007

+1+ ID + EventDate + EventType +
+2+ 111 + 1/1/1995 + Visit +
+3+ 111 + 1/30/1995 + Vaccine +
+4+ 111 + 4/22/1998 + Visit +
+5+ 222 + 7/7/2000 + Survey +
+6+ 333 + 8/8/2001 + Labs +
+7+ 333 + 8/22/07 + Pharmacy +
+8+ 333 + 8/22/07 + Visit +

shade the rows with alternating colors so that all the data pertaining to a give ID can be easily spotted. In the example above, I would need A2:C4 to be shaded one color, A5:C5 another color, and A6:C8 the first color again.

View 9 Replies View Related

Conditional Format Whole Rows With More Than 3 Conditions

May 14, 2012

I am needing to format a spreadsheet using 2003 which only allows 3 conditional formats, but I have 4 conditions.

I need to highlight the row if column W has a

G - green (colorindex = 35)
R - red (colorindex = 3)
Y - yellow (colorindex = 36)
O - orange (colorindex = 44)

how I can do this?

View 7 Replies View Related

Apply Conditional Format On Several Rows?

Aug 12, 2012

I have a conditonal format of cells across Row 2 based on the contents of cell D2. The formula for the conditional format is

=$D$2="NONE"

The formatting applies to these cells:

=$F$2:$G$2,$I$2:$K$2,$M$2:$N$2,$O$2,$T$2:$V$2,$X$2:$AA$2

I would like to fill in the same conditional format down to 51 other rows but the formatting for each row should be based on the contents of its own cell i.e. $D$3, $D$4, $D$5 etc. How can I do this with code?

View 2 Replies View Related

Deleting Rows Without Conditional Format?

Jul 3, 2013

I have a report with over 12,000 rows of info.

I have set conditional formatting to highlight yellow if the rows contain a certain word.

I need to delete all the non-highlighted rows.

View 2 Replies View Related

Conditional Format Multiple Rows?

Feb 19, 2014

Is it possible to alternate how a condition works over many rows? Say column C has work order numbers eg. C2:C7 shows WO1234, C8:C15 shows WO1235, C16:C25 shows WO1236 and C26:C30 shows WO1237. What I would like to do is have rows 2:7 red, rows 8:15 blue, row 16:25 red, rows 26:30 blue and repeat to end (the range would be column A to column N. Ive used =MOD(ROW(),2) for every second row but am not sure how to get it to look at many rows and alternate as needed.

View 6 Replies View Related

Conditional Format - To Do All The Rows At Once Instead Of One At A Time

Nov 17, 2008

I have a list of 50 sites (in rows) and their revenue for the last 12 months (in columns). I would like to use conditional format to highlight the highest number in each row but I can only do this one row at a time using top/bottom rules (top 1%). Is there a formula that I can use to do all the rows at once instead of one at a time?

View 9 Replies View Related

Quick Re-format/add Rows Using MACRO

Feb 18, 2009

create a MACRO/module that will take teh above example rows 1 thru 6 to look like rows 9 thru 30. See below ....

View 9 Replies View Related

VBA Copy, Paste Rows And Format

Apr 19, 2006

I have been trying to set up this macro to copy and paste rows where a tick box is checked. The macro needs to transfer the row A:H over to sheet 2 then insent the data acording to property title either A, B, C etc. into formated tables which then feed into a bar and pie chart. I have tried posting this question up in sections hoping that i could get the bits of code i need and then work out how to do the macro but I have had no luck since my VBA skills are poor. I have attached the file

Private Sub CommandButton1_Click()
Dim lRow As Long, lRow1 As Long, lRow2 As Long
Dim Target As Range
Dim vTemp As Variant
Dim WS2 As Worksheet
Set WS2 = Sheets("Sheet2")
lRow = WS2.UsedRange.Row + WS2.UsedRange.Rows.Count
For Each Target In Range("I1", Cells(Rows.Count, "I").End(xlUp).Address).SpecialCells(xlCellTypeConstants)
vTemp = Target.Value
If VarType(vTemp) = vbBoolean Then
If vTemp = True Then
vTemp = "*"
On Error Resume Next
vTemp = WorksheetFunction.Match(" Total*", Range("A" & Target.Row, "A" & Rows.Count), 0).....................

View 2 Replies View Related

Conditional Format Rows 4+ Criteria

Mar 7, 2007

What I am looking to do is format a row so that it is highlighted if one cell in that row meets one of two criteria or if another cell in that row meets one of two criteria: I was hoping to use the excel conditional formating tool but I'm stumped as I need 4 criteria and it can only do 3 criteria.

The cells in question require a greater than or less than criteria. If could use something like: Formular is =$AJ16 <= -0.11 or =$AJ16 >= 0.11 and
Formular is =$AK16 <= -0.11 or =$AK16 >= 0.11 I would be very happy but it won't let me do this - is there a way it can be done?

View 2 Replies View Related

Cell Format Dependant On Rows

Mar 13, 2007

I have

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
Select Case .Value

Case "(None)": .Interior.ColorIndex = Null
Case "One": .Interior.ColorIndex = 38
Case "Two": .Interior.ColorIndex = 18
Case "Three": .Interior.ColorIndex = 35
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
End If

End Sub

My problem is that columns A to H are yellow(36 I think) and are merged cells.
And Cells AW2 to BD2 are also yellow. The cells inbetween are white.

At the moment when the Cell value is "(None)" the cells turn white, I dont want this. I would like them to go to default.

View 9 Replies View Related

Conditional Format Entire Rows In One Go

Feb 8, 2008

I've got a pivot table that shows different activities their cost, budget and percentage complete amongst other things. I would like to use conditional formatting to turn the font of a whole row grey if that activity is a 100% complete (exactly more then 99,5% complete in this case). I can do this with conditional formatting by selecting the individual rows and then setting the conditions. Example: selected row =$45:$45, Formatting condition =$J$45>99,5%.
However I've got 1500 rows to do....how can I set this condition for all rows in one go?

View 2 Replies View Related

Conditional Format Rows To Highlight Every Other Row With A Value Different Than Previous

Feb 11, 2014

do a conditional format rule that will highlight every other row of my excel spreadsheet when the value in a specific Column (say Column A) differs than the previous.

I've attached a sample worksheet with what I want it to look like after the conditional format rule is applied (every other row highlighted in light blue). The rule needs to apply to all rows in the worksheet beginning with ROW 2 (I don't need the rule to apply to the column header which is in ROW 1).

View 4 Replies View Related

VBA Set Protect Sheet - Format Columns And Rows

Jun 11, 2009

I have VBA code to set a password on multiple worksheets in a workbook. I new need to force a check in the "Format Columns" and "Format Row" checkboxes. The current VBA code is:

View 4 Replies View Related

Conditional Format Comparing Data Between Rows

Nov 11, 2009

(Core starting issue solved but other threads were created to continue with trouble spots.)

I'm terrified to use VBA just yet, so right now I'm determined to highlight values in my spreadsheet just using conditional formatting.

I have 5 plus 1 columns of data. For example,

A B C D E....F
3 2 4 5 7....2
4 6 1 3 4....1
4 7 4 5 8....4
2 1 5 3 9....1

I successfully applied a MIN formula to compare between columns on each
row and display in F the MIN value. Notice each cell going down F has the
minimum value in analyzing across each row.

Now, I *also* want to apply a formula on the whole F column to compare these resultant MIN values
and give me the MINIMUM value between those as well, so now I'm comparing across
rows. Got it? Now then.....I want the ENTIRE row that has this MIN value in F to be highlighted, because that will be the best choice for the application of what I'm doing. Did you understand this paragraph. It might have been confusing, but I think I said everything correctly, so read it carefully.

I've tried all sorts of "Formula is", "Cell value is", dollar sign this, dollar sign that, no dollar sign this, <, >, =, highlighted ALL the block of data, highlighting JUST the column across the rows of which will be compared, only highlighted one cell, but nothing has worked.

Is my problem that you can't have 2 formulas in one cell? So then I thought I should ADD another condition, but nothing works.

Let me throw in one more element that I did not state. If anywhere down the F column there is a 0, I don't want that to be my minimum. I want it to evaluate and give me the minimum value greater than 0.

Please read everything I wrote carefully. It might be confusing, but I hope someone has an answer, because it sure has frustrated me.

Now, I'm thinking that VBA programming may give me answers, but I am so terrified of macros, I don't know how to begin that. I have this impression based on the many sites I've looked at on the internet that many people do macros and it's quite easy, but it's funny to me that I am so scared. There's something about me freaking out that I won't have a period or a quote in the right place and I'll stress my day away over it.

View 14 Replies View Related

ADVERTISEMENT