Find a row based on conditional formatting

[Chris L]

I work with big spreadsheets of heat treating data, time pressure and temperature.
I need to find the first row of data that a specific range of columns are highlighted green, bold that row and add text in a cell of that row.
The conditional formatting is looking at another range of cells at the top of the workbook so the find can be based on values instead of conditions.

in the attached example I need to find and bold row 2133 and add text at K2133

 

[AlanSidman]

You cannot search in Excel based upon Conditional Formatting, but you can search using the same criteria employed that resulted in the conditional formatting. Without knowing that criteria, it is not possible to provide you with a solution.

 

[Chris L]

In the attached screen shot I will create a table at the top of the worksheet with the values for the conditional formatting.
Then use those values to format C2:L1800 and B2:B1800
The cell values are temperature and pressure readings as my process cools down.
I need to find the first row that B<= R3 & C:L,=Q3

 

[p45cal]

Supply an excel workbook rather than a picture of one!
Test on the active sheet:

Sub blah()
Set myrng = Intersect(Range("B2").CurrentRegion, Columns("B:L"))
Set myrng = Intersect(myrng, myrng.Offset(1))
maxPress = Range("R3").Value
maxTemp = Range("Q3").Value
xx = myrng.Value
For rw = 1 To UBound(xx)
  Failed = False
  If xx(rw, 1) < maxPress Then
    For colm = 2 To UBound(xx, 2)
      If xx(rw, colm) > maxTemp Then
        Failed = True
        Exit For
      End If
    Next colm
      myrng.Rows(rw).Font.Bold = True
      Cells(myrng.Rows(rw).Row, "N").Value = "New Info Here"
      Exit For
    End If
  End If
Next rw
End Sub

It only tests against the maxima, ignoring the min values.
It tests for less than the max pressure:
If xx(rw, 1) < maxPress Then
which you might want to tweak:
If xx(rw, 1) <= maxPress Then

Likewise it tests for values greater then the max temperature:
If xx(rw, colm) > maxTemp Then
which, again, you might want to tweak:
If xx(rw, colm) >= maxTemp Then

 

[Chris L]

My bad.
Here is a typical data file and the current macro for sorting data.
There is a lot going on here.
based on how my system records I can track when stage 19 and 27 start and stop.
I can find the end of stage 22 and 24 its the beginning we have to do manually.
In this instance I am only looking at Temperatures to drop below or equal a maximum.
I have other macros that I would need to look at both.

Any help would be greatly appreciated.

 

[p45cal]

There is a lot going on and I've been stepping through your code trying to work out your intentions and I think I've got a fairly good grasp of it.

At one stage you apply some conditional formatting on Sheet4 (which later is called Formatted). The formulae for conditional formatting use values you've hard-coded in cells M2:R3. The data at that point in the code is in several blocks, separated by white space:
1st block (rows 2:513) containing stage nos. 4,5,6,7,12,13,14 &18 (and some 19 which had been adjusted to 18 when deciding what to copy and what to highlight).
2nd block (rows 750:869) containing only (part of) stage no. 19.
3rd block (rows 900:1119) containing only stage no. 20.

Now the first bit of conditional formatting is placed in cells C2:F902, whose rows cover completely the first 2 blocks, but encroaches into the 3rd block by a few rows. Similar things happen for the next block of conditional formatting. So I doubt that's your intention.

Q1.
What decides the size extents of the ranges you want to be conditionally formatted? It seems to be related to stage numbers, but to the adjusted ones or the original ones? [At the time you apply conditional formatting the original stage nos. have been restored to column I by deleting columns I:L] Or something else? Can you be very specific about this please?
Q2.
More generally, when you've finished processing a run, drawn the graph, created a Run Data sheet and a Formatted sheet, saved and closed it and forgotten about it. When someone opens that file again what will they be interested in seeing? I imagine the graph and the Run Data for sure, but will they be interested in viewing the Formatted sheet, with its conditional formatting, bold rows and occasional comment? I ask this because if the Formatted sheet is not really for human viewing, I can easily create the graph and Run Data sheet from the original data sheet (or a copy of it) without lots of filtering and copying.

 

[Chris L]

This particular cycle is the most complex we perform right now.
Most are just heat and pressurize to one set of parameters, hold for a specified time and cool down.

Q1 The "Holds" stage 19, 27, 22 & 24 are all a specific time.
I always grab a few extra rows after a hold just in case the start and stop lands between 30 second data logs to show we met the customers time requirement.
Our control software was written to add the notation "Delta Pause" if both Temperature and Pressure are not in spec, unfortunately only on the low side. This is why I change stage 19 Delta Pause to stage 18 and the same for stage 27. This gives me the exact row that these two holds start.

I don't get that when its cooling down to a set of parameters
For this cycle we go in after running this macro and highlight the first row of stage 22 and 24 that are all green.

Q2 So after its all done the Graph and Run Data pages are printed, signed and sent to the customer.
The formatted tab is copied to another workbook, columns G:J are removed and this is emailed to the customer.
We save the file after the macro is run.
This data has to be available for a customer, NADCAP or AS auditor if the want to see how we verified the cycle parameters.

 

[vletm]

Chris L ---- A chart could tell ... a lot.
You wrote: I am only looking at Temperatures to drop below or equal a maximum.
A red line is Stage -- others are Your named temperatures ('high values' = 3rd Legend line = in 2nd axes)
Which moments are You looking for?

 

[p45cal]

I suggest creating the Formatted sheet by only copying the raw data sheet and then adding/deleting/highlighting stuff.
Reasons:
It's more straightforward and also more robust and faster; I'd worry, since your stage numbers don't always ascend, that when you filter for groups of stage numbers and copy blocks to another sheet, that that sheet might not have the stages in the correct time order.

Q1 The "Holds" stage 19, 27, 22 & 24 are all a specific time.
I always grab a few extra rows after a hold just in case the start and stop lands between 30 second data logs to show we met the customers time requirement.

I propose you don't do this, but if you must, do it by only 1 row. If it happens that the target temperatures and pressures are not met in time, then maybe overlap some more - the code can do this, as well as highlighting this failure to meet targets.

Our control software was written to add the notation "Delta Pause" if both Temperature and Pressure are not in spec, unfortunately only on the low side. This is why I change stage 19 Delta Pause to stage 18 and the same for stage 27. This gives me the exact row that these two holds start.

The final Formatted sheet shows the un-adjusted stage numbers, so it seems you only changed the numbers for filtering/copying. Code can determine where to put the likes of Intermediate Hold Start and Hold Start and the bolding.

I don't get that when its cooling down to a set of parameters
For this cycle we go in after running this macro and highlight the first row of stage 22 and 24 that are all green.

Again, this is easy for the code to do without moving anything around. A variant of the blah macro in msg#4 will do it.

Q2 So after its all done the Graph and Run Data pages are printed, signed and sent to the customer.
The formatted tab is copied to another workbook, columns G:J are removed and this is emailed to the customer.
We save the file after the macro is run.
This data has to be available for a customer, NADCAP or AS auditor if the want to see how we verified the cycle parameters.

It's all quite do-able. I imagine that you need to do this for a wide range of runs with different protocols/recipes and different thresholds; currently you use hard-coded values, but these values must come from somewhere - you don't do all runs with the same thresholds do you? Where can that data be found and made available to the code?

One question I do have is about the Run Data sheet which seems to only deal with stage 27. Your current data doesn't need any adjustment of stage 27 numbering so in other cases, does that Run Data sheet summarise original stage 27 or adjusted stage 27?

Finally, to whet your appetite, the attached contains just your original raw data sheet and 30 lines of code, run by clicking the button on the sheet.
It creates a new sheet which will become the Run Data sheet after a few more additions and formatting.