Setting limits and changing text colour if limit is exceeded

[ncook12]

I have set up a spreadsheet which has lots of VBA code mainly because the data set is dynamic. I have got a calculation called Average at the end of each column. I want to set in that formula that if a limit is exceed or falls below a certain number that the text in the field changes colour. I can not set it on the spreadsheet because the number of records will be updated regularly. Can anyone help?

 

[RoyVidar]

Hi!

Could'nt you just use the conditional formatting thingie?

Either by doing it thru the menues, or use VBA to select the columns/rows where you wan't it applyed?

Roy-Vidar

 

[VBAjedi]

Conditional formatting is probably your best bet. You can either just select the whole column, then apply conditional formatting to it (that should bypass issues with adding/deleting rows).

Or, if that's too much of a shotgun approach, you can write a sub that figures out what the range of the data is each time the sub is run. Set it up so the first data row never changes, then find the last data row with something like:

LastRow = Worksheet("MySheet&quot.Cells.Find(what:="*", after:=Range("IV65536&quot, searchorder:=xlByRows, searchdirection:=xlPrevious).Row

(There's other ways to do this, but I like this way because it doesn't rely on Excel knowing what the last used row is). Then apply your conditional formatting (via code) to the target column for the specified rows (use the macro recorder to see the syntax for applying conditional formatting).

You'll just have to decide how you'll call that sub (a button, or some workbook event like "Open", or some worksheet event like "Change&quot.

Let me know if this helps you!

VBAjedi

 

[ncook12]

I haven't been able to get it to work. VBA won't accept the lastrow part. And i have looked around and the only other thing i could find was Lastinrow - which is don't want. IS there anything else i should do?
Thankyou

 

[RoyVidar]

Hi!

My approach is always to record actions, and tweak them afterwards. I've been using to much time in figuring out how to program this and that in Excel, to later find out it could easily be recorded.

OK - if I understand you correct, you wan't conditional formatting in your last column. The last column might be column c, d ...n ie dynamical based on the imported dataset.

Now, first I would remove all previously used conditional formatting. The Excel Macro Recorder provides the following code:

' select all cells with conditional formatting (available thru F5 in Exel)
activecell.specialcells(xlcelltypeallformatconditions).select
' delete current conditional formatting
selection.formatconditions.delete

After that, I'd probably place my cursor at the top left corner of the sheet, make Excel move the cursor to the last cell in the row (= last column), select entire column contents and apply conditional formatting.

Excel Macro Recorder provides this (with just minor tweaking):

Range("A1&quot.Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="10", Formula2:="15"
With Selection.FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Font.ColorIndex = 41 ' Text color blue
.Interior.ColorIndex = 6 ' Back color (pattern) yellow
End With

The conditional formatting "selecting" can easily be modified to i e cell references

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$C$35", Formula2:="=$B$35"

btw - the lastrow thingie in VBAjedi's post, (should) get the row number of the last row providing lastrow is declared as a long (I think) - haven't used his notation, so I'm not entirely sure how it works.

HTH Roy-Vidar

 

[ncook12]

Thankyou both for your help it works. Thankyou.

 

[VBAjedi]

Just by way of explanation:

LastRow is just a variable name I picked (so you won't find anything on it in the help files). Leave it as a variant ("Dim LastRow&quot and it should be fine. The method I'm using to find the last used row is to do a bottom-up search for anything ("*&quot, starting in the last possible cell on a worksheet ("IV65536&quot. I can only guess, but probably the reason you had trouble is that you didn't put your worksheets name in place of my sample name "MyWorksheet". Sorry I didn't mention that in my post.

The reason I use this approach is that Excel's built-in system for tracking the last used row sometimes gets confused, or represents something you don't consider to be the last used row. For example, it might consider a row to be "used" if it has formatting applied, even if there is nothing in the cell! My approach finds the last cell with actual contents (which is almost always what I'm interested in).

Anyway, glad you have a solution that works now!


VBAjedi