[TypicalUser]

Having difficulty with the following. I'm thinking it should be simple, but am over-complicating it.

Basically I have a range of data(numbers) that could be any numbers of columns wide and any number of rows deep. There would be no data to the right or below this data and the data normally starts in B10, but depending on the user could be B9 or B11. There are no blank cells in the data, it will be some number or zero.

What I want the code to do:
evaluate the data and conditionally format each cell based on the cell to its immediate right for the whole range of rows and column - 1 (no sense in evaluating last column against blanks). Example: if cell B10 < C10, highlight in light green.

I also want to produce a total at the bottom of each column that would conditionally format the lowest total(s) in yellow

And last, I want a countif total at the bottom of the column I specify (by placing any data in row 1 of that column) that will give me a total of all cells in the column (of data) that is not zero ("<>0") [it has to skip the "totals" row.

I've been racking my brain on this starting with recorded macro and then adding snippets I see in many other responses, and am completely butchering it.

Any help would be greatly appreciated.

[bomb #21]

"There would be no data to the right or below" is half the crucial information.

The other half is "what about to the left or above?"

That might give us a starting point, facilitating (example):

Range("B" & Rows.Count).End(xlUp).CurrentRegion.Interior.ColorIndex = 3

(note: no physical selection of the range occurs with the above, which might be good ; it depends how you want to "conditionally format")

"I also want to produce a total at the bottom of each column"

Take a while to contemplate this:

Sub test2()
x = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(1, 1).Cells.Row
'get top row
y = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(, 1).Cells.Count
'get rows in range
z = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Columns.Count
'get columns in range
Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, z) = "=SUM(B" & x & ":B" & (x + y - 1) & ")"
'make some sums
End Sub

Welcome to the board.

[TypicalUser]

Thanks, I understand for the totaling - still confused for the conditional formatting.

Here is what I produce when I record the macro. The problem however is that the macro reads and records a specific cell for the ending point of the array. I want the macro to be dynamic so tha it will work for any array (combination of rows and columns)

Sub test3()
'
' test3 Macro
'
' Keyboard Shortcut: Ctrl+r
'
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=C10"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("B10:I15").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

So in my case, my data started at B10 and goes right and down to J15. Since I only want conditional formating on after I do an {End}{Right} I move LEFT one column and paste the format there. I'm sure there is probably a way to do this cleaner than this. I've been trying to use activecell.offset with variables to get the range and inserting variables as arguments for "Formula1:=" but am getting further from my solution.

[TypicalUser]

Further clarifying:
B10 should validate against C10; C10 against D10, B11 against C11 and so on. Basically copying the relative reference. When I have been trying various methods, I often end up with everything looking at C10 which would be incorrect.

[bomb #21]

"still confused for the conditional formatting"

I meant it depends whether you want code to "invoke" the built-in conditional formatting function, or do the formatting itself. The latter would be something like:

For Each Cell In Range("B10").CurrentRegion
If Cell < Cell.Offset(, 1) Then
Cell.Interior.ColorIndex = 4
End If
Next Cell

[bomb #21]

"I've been trying to use activecell.offset with variables to get the range and inserting variables as arguments for "Formula1:=" but am getting further from my solution."

Well then, try a different tack.

1. Use code to figure the range co-ordinates

2. Use those to insert a named range

3. Refer (instead) to the named range while the code's applying the conditional formatting.

HTH

Sub test()
y = Range("B" & Rows.Count).End(xlUp).Row
x = Range("B" & y).End(xlUp).Row
z = Range("B" & y).End(xlToRight).Column

ActiveWorkbook.Names.Add Name:="MyRange", _
RefersToR1C1:="=Sheet1!R" & x & "C2:R" & y & "C" & z

Range("MyRange").FormatConditions.Delete
Range("MyRange").FormatConditions.Add Type:=xlExpression, Formula1:="=B5<C5"
Range("MyRange").FormatConditions(1).Interior.ColorIndex = 6
End Sub

(no selecting involved)

[TypicalUser]

Thanks, this is getting me almost all of the way there...

Adding a few of your recommendations, I have this:

Sub newtest()

x = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(1, 1).Cells.Row
'get top row
y = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Resize(, 1).Cells.Count
'get rows in range
Z = Range("B" & Rows.Count).End(xlUp).CurrentRegion.Columns.Count
'get columns in range
Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, Z - 1) = "=SUM(B" & x & ":B" & (x + y - 1) & ")"
'make some sums

ActiveWorkbook.Names.Add Name:="MyRange", _
RefersToR1C1:="=Sheet1!R" & x & "C2:R" & y & "C" & Z

For Each Cell In Range("MyRange").CurrentRegion
If Cell > Cell.Offset(, 1) Then
Cell.Interior.ColorIndex = 6
End If
Next Cell
End Sub

The issue I am having is that my first column contains data (text) that I don't want evaluated in the conditional format loop - same for the last column (will always highlight as its value will nearly always be greater then the empty cell it is next to).

How do I get "MyRange" to be R1C2 to RyC(z-1)?

I tried doing it within the RefersToR1C1, but that appears to not be the place to do that.

[bomb #21]

If "first column" = A then, we never actually covered "what about to the left or above?".

y = Range("B" & Rows.Count).End(xlUp).Row
x = Range("B" & y).End(xlUp).Row
z = Range("B" & y).End(xlToRight).Column - 1

?