Conditional Format Comparing Data Between Rows

[Danexcel]

Hello,

(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.

Great.

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.

Anyway, thank you for giving thought to my problem. I'll be so happy!! when this is figured out.

-D

[StephenR]

I think you can safely put off VBA for a while yet. If I understand correctly, select your range A1 to E4 or whatever and enter this in the CF box (Formula Is):

=AND($F1>0,$F1=MIN($F$1:$F$4))

and add your desired formatting.

Does that do what you want?

[Danexcel]

Hello,

Thanks for the suggestion. It didn't work, sadly. The "real" sheet I'm working with actually starts down on row 5 all the way down to line 604, but I honestly don't think that has anything to do with the problem.

Anyway, let me give a little more clarification. Here is an actual shot of my sheet. I hope you can understand what I want to do. I manually formatted the data for this post. Thanks!

[StephenR]

Well you'll need to adjust the actual ranges to your file. If problems persist, please attach a sample workbook rather than a picture.

[Danexcel]

Hello,

Thanks for even noticing my post and trying to help! You are great.

Ok, here is a sample. On the right side of the spreadsheet is some highlighting.

You can see orange highlighted numbers. Do you see that "4" is highlighted? That's along the lines of what I want. Highlighting is not necessary for that, but it shows what to focus on. What it did is highlight the MAX in comparing between the rows, up and down.

Now, that column taking first priority, I then want to move to the values in the column to the left. Of the four "4" selections, I want the ENTIRE row highlighted that has the MIN value greater than 0. So in this sample that I have sent here, two rows ALL THE WAY ACROSS (even across as far as the A column) should be highlighted, that being rows 8 and 10, since 7.36 is less than 23.36. If there were a 0 in that R column, I'd want it ignored.

By the way, the brown highlighting is the formula you suggested originally.

Now that I think you understand what I want, what I *Really* want to do is.......get ready

have another worksheet as the "answer page" and have displayed those two highlighted rows plucked out from this worksheet. I'm wondering if that will require macros at this point, or still could I get away with linking a zone of rows to be able to display these "answer" rows, which would not be determined, right, because it depends on the numbers how many rows will be highlighted.

Ok, hopefully you have a better idea of what I want. Thanks so much.
-D

[StephenR]

So you don't really want the rows shaded, you want them moved to another sheet?

[Danexcel]

Yes, basically.

But I'd like the (so called) easy step of highlighting to be on the "data" sheet just to refer back to.

But yes, what it boils down to is that actually I have 600 rows of data (I sent like 10 of them for the sample file) and I'd like to pluck the highlighted rows out and have it presented in a simple form onto another worksheet. Like, if of all the rows, say six rows ended up being highlighted, then only those 6 rows would be shown on the "simple" page. If in another situation, 3 rows were highlighted, then only the 3 rows would show on the simple page.

I'm just assuming (because I know nothing) that this might require macros somehow. But I'm learning and have a positive mindset to figure this out!

Thank you!

[StephenR]

See if this gets you most of the way there. I haven't shaded the rows on the first sheet but put a coloured border round them so as not to disrupt existing formatting.

Please Login or Register  to view this content.

[Danexcel]

Thank you!

So far I'm pleased with the progress.
I'm studying the macro, and what I did was "create" a new one and clicked run and it worked. Now I'm trying to tweak the lines so that it will work with the full document. You used Sheet1 and Sheet2, and I'm changing it to Sheet4 and Sheet5, but so far nothing is working, so I'm starting to get scared like I expressed earlier about trying to find the periods, commas, etc, and checking all is in the right place. Do you have a suggestion as to which elements might vary due to change in reference to worksheet?

I will also want to learn how to put on the simple page a button that will start the macro. That will be my next project.

Thanks a lot!

[Danexcel]

As I learn about VBA/macros, does the following below seem like a reasonable adjustment to the macro program you sent? The reason I said R5:R604 was because R4 actually has text and not a number. The sheet of data is actually Sheet4 and the "simple" page is Sheet5. It didn't work by the way. So do you notice any glaring mistakes? Thank you!

Option Explicit

Sub x()

Dim rng As Range, rData As Range, nMin As Double, vRow(), i As Long

With Sheet4
Set rData = .Range("R5:R604", .Range("R5:R604").End(xlDown))
End With

Sheet5.UsedRange.Clear

For Each rng In rData
If rng.Offset(, 1).Value = WorksheetFunction.Max(rData.Offset(, 1)) Then
If nMin = 0 Then
nMin = rng.Value
i = i + 1
ReDim Preserve vRow(1 To i)
vRow(i) = rng.Row
ElseIf rng.Value <= nMin Then
nMin = rng.Value
i = i + 1
ReDim Preserve vRow(1 To i)
vRow(i) = rng.Row
End If
End If
Next rng

For i = LBound(vRow) To UBound(vRow)
With Sheet4.UsedRange.Rows(vRow(i))
.BorderAround LineStyle:=xlContinuous, ColorIndex:=3, Weight:=xlMedium
.Copy
Sheet5.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
End With
Next i

End Sub

[StephenR]

Have had lots of problems accessing the site, hence delay in responding.

Please use code tags as per forum rules, and then I'll have a look.

The only factors with a bearing will be the differences between your actual file and the one you posted. So it's generally better if you post a sample which represents your actual file and then you don't have to spend time adjusting the macro.

[Danexcel]

Thank you. As I learn about VBA/macros, does the following below seem like a reasonable adjustment to the macro program you sent? The reason I said R5:R604 was because R4 actually has text and not a number. The sheet of data is actually Sheet4 and the "simple" page is Sheet5. It didn't work by the way. So do you notice any glaring mistakes? Thank you!

What you said about posting a similar file to the real one is better I understood. I don't believe there should be a big difference; or I might not know enough about vba to say so yet maybe. But anyway, I'll do what I can to learn.

Please Login or Register  to view this content.

[StephenR]

It didn't work by the way

Does this mean you got errors or it didn't do what you want.

For sheet names, it might be easier if you refer to their names as they appear on the sheet tabs, e.g. instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

or whatever it is.

Change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Btw this assumes you have no blanks in column R.

If problems persist, post your actual file or a representative sample.

[Danexcel]

Thank you! Here's the popular blow by blow response to each of your issues raised
.

Does this mean you got errors or it didn't do what you want.

SOLVED! Ignore this because you successfully cleared that up in that next response you had sent.

For sheet names, it might be easier if you refer to their names as they appear on the sheet tabs, e.g. instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

or whatever it is.

Had changed the names on the tabs so they'd match the exact name in the code, so I *assumed* no problem.

Change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Btw this assumes you have no blanks in column R.

If problems persist, post your actual file or a representative sample.

When you say "no blanks?" I'll answer that there are some blank cells like R2 and R3, but for the range from R5 down to R604 it is solid with no blanks. Is that OK. Now as I type I see that you are wanting to go back to just including *all of column R. Well, there is text in R4. What I'll try I suppose is just delete that text and, well, I don't know what to do with the blank cells around the range I specified. I'll give it a go.

[Danexcel]

UPDATE:

Two things I did. I deleted text in the other R cells.

I changed, as your request, the names of the sheets to Sheets("Sheet4") and Sheets("Sheet5").

And it is working!

Getting great results on the Sheet 5.

I then now have tried to type in headings in row A for these results to identify this numbers, but I see now that every time you run the program it wipes all that away and just gives you the result data. Is there a way to lock in a row of text for row A?

Also on this same page, is there a way to designate some fields at the top to enter numbers in which will link to the other sheet?

Also on this same page, I want a button to run the macro.

I feel so close now. Thank you!

[StephenR]

Is there a way to lock in a row of text for row A?

Yes, perhaps you could add desired headings to your file?

is there a way to designate some fields at the top to enter numbers in which will link to the other sheet?

Not sure I follow - can you rephrase or add an example to your file?

I deleted text in the other R cells.

As an aside, blanks in R1-R4 don't matter, it's just the cells below R5 I meant.

[Danexcel]

Quote:
Is there a way to lock in a row of text for row A?

Yes, perhaps you could add desired headings to your file?.

Yes, before I had asked about this I tried to add the headings on the result page, but unfortunately when I ran the macro again it wiped all the headings clear and just gave the resultant rows. I'm assuming it has something to do with the word "clear" in the prgramming?????

Quote:
is there a way to designate some fields at the top to enter numbers in which will link to the other sheet?

Not sure I follow - can you rephrase or add an example to your file?

On Sheet4 (where the result rows after the macro is ran) there are cells (I said "fields" assuming the same as a "cell" but maybe wrong word, sorry) that allow the user to enter a number. That number will automatically be placed in a linked/indexed cell on Sheet5. So I want the user to only interact with one screen, not two.

Thank you so much.

[Danexcel]

Hello, I just want to take a time out and thank you, StephenR, for helping me understand VBA. I started this post saying I was terrified of VBA, but now I'm slightly more comfortable with addressing it. I still have a long way to go, but you kind of threw me in the deep end and I'm kicking and paddling now, and successfully staying afloat.
I still have some steps to go on this project. I'm going to continue to dig around, refer to others' posts, and see if I can figure this out. I have a sense though that every project is different? I'll keep working to tear down this barrier I feel towards vba. Thank you sincerely for all of your help.

[Danexcel]

Duplicate message

[Danexcel]

Hello,

I need help with the following code I was provided.
There is a point in the code you can see that indicates that rows are outlined in red.
If I change a value and run the macro again, the red-outlined rows remain. I want them cleared. I want different rows to be outlined based on the macro.

Any ideas? I'm thinking I need to put a "clear" command somewhere in here.

Thank you,

Please Login or Register  to view this content.

[Danexcel]

I was still looking for any advice on the last post on this issue. Thanks to anyone who might have some insight on this. Thank you.

[StephenR]

You may have to add all this at the beginning of the code:

Please Login or Register  to view this content.

[Danexcel]

I tried to run the macro with the added lines and it said "Can't execute code in break mode." What is break mode?

[StephenR]

From the VB editor window go to Run > Reset.

[Danexcel]

Working working.
It took little tweaking and optimum placement searching for where to slip in the code, but now it's working, and I'm almost there! Thank you for you help.

The last thing I'd like to ask how to do is how to have the list of results begin on a specified row downwards. I'd like to have headings at the top of the page that do not get erased like they do now when I run the program.

Thank you!

[Danexcel]

I just discovered that when I run the macro, a message pops up saying it can't find the file as the old name it used to have.

Where do I indicate to VBA the name change?

[Danexcel]

I'm currently searching this site trying to find perhaps a simple line of code that I can paste into my code to attach the vba program to the excel file it is within but to no avail.

I'm not understanding this concept of detachment of the vba code from the file it is within. Once I can grasp this, I should be good to go. One thing I thought was interesting is as I study the vba code line by line, it doesn't mention this old file name it so desperately wants to find. I clicked in the project file view window to the left of the vba editor pane and saw no hints of the old file name, so why is the message box/vba program remembering this old file name??

[StephenR]

A little confused as to what you're doing now - can you post current code? Possibly you should start a new thread as this sounds like a different question.