Search for first instance of number greater than x -VBA Excel

[dhigby]

Can you help me please? Here's what I want to do.
o for [column a] equal x, look at [column c]
o if [col c] greater than 50, highlight columns a - c in that row in yellow. else, next row.
o once one number greater than 50 is found and highlighted, go to next [col a] (in other words, don't highlight all numbers greater than 50, just the first one...)

Hope this is clear. Thanks in advance for your help.

 

[SkipVought]

Hi,

What have you tried so far?

ALSO, please post an example of values in these columns and what result you wish to achieve.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhigby]

Hi Skip,
here is a sample of the data:
MOD01 0132 32
MOD01 0131 37
MOD01 0130 41
MOD01 0129 46
MOD01 0126 48
MOD01 0125 53
MOD01 0124 57
MOD02 0132 38

In this example, the line MOD01 0125 53 Would be highighted. My standby of recording my steps is not working for me here. I think i would be able to do the for loop on the column 1 part, but i have no clue how to do a find for a number that is not exact. I can't look for Col C = 53 because it won't always BE 53. it is unpredictable. Just looking for the first number over 50. Thanks again

 

[PHV]

Again, WHAT HAVE YOU TRIED SO FAR, and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Check out the MATCH spreadsheet function. The MatchType of 1 will work for you. No VBA needed, if you use Format > Conditional Formatting...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhigby]

The match function seems to be looking for a specific value. The value I am seeking is the first number greater than 50, which can be variable.
Is there a way to get the match function to look for a variable?

The conditional formatting seems like it would highlight all rows with value greater than 50, not just the first instance.
Is there a way to get conditional formatting to highlight just the first instance?

Appreciate all your ideas!

 

[SkipVought]

Au contraire, on BOTH mentioed issues!

Using the MatchType of 1, "finds the largest value that is less than or equal to lookup_value."

Using CF, can highlight ONE CELL that meets the criteria.

I have done it, using your example!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhigby]

I'll try it! Thanks!

 

[jhony99]

set xlobj = createobject("excel.application")
set wrkbook = xlobj.workbooks.open("give the excelsheet path")
set wrksheet = xlobj.worksheets("give sheet name")
set x = wrksheet.usedrange.find("give the word/string what u have to find/fetch")
for each x in wrksheet.usedrange
if x = "string to find" then
x.interior.colorindex =40
end if
set x = wrksheet.usedrange.findnext(x)
next

I THINK IT WILL HELP YOU TO FETCH THE REQUIRED STRING IN A EXCEL SHEET...
PLEASE LET ME KNOE IF THERE ANY QUERRY