1

Topic: Formulas in a cycle

Hello, help with job performance, please (I study generally on the house-keeper, therefore it is difficult to assort all it). The task such: it is necessary to paint over the maximum and minimum value in each column of the table. The table can be set arbitrarily, i.e. though 4 columns, though 10. Though 6 lines, though 100.
I understood how to paint over, how to count an amount of columns in the table, how to create a cycle. But I do not understand, how to me so to inscribe all it in a cycle. That is, I simply wrote down a macro, fulfilled actions, stopped record and looked that turns out. Naturally the program wrote down columns through letters and all operator in the code are constructed on it, and I need to alter all on No a column.
Here the given code (it certainly is not finished). Here in the formula = ($: $) =1 I need to replace A on i.

Sub Makros6 ()
kol_stolb = Cells (1, Columns. Count).End (xlToLeft).Column
For i = 1 To kol_stolb
Columns (i).Select
Selection. FormatConditions. Add Type: = xlExpression, Formula1: = _
"= ($A: $ A) =A1"
Selection. FormatConditions (Selection. FormatConditions. Count).SetFirstPriority
With Selection. FormatConditions (1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
End With
Selection. FormatConditions (1).StopIfTrue = False
Columns ("A:A").Select
Selection. FormatConditions. Add Type: = xlExpression, Formula1: = _
"= ($A: $ A) =A1"
Selection. FormatConditions (Selection. FormatConditions. Count).SetFirstPriority
With Selection. FormatConditions (1).Interior
.PatternColorIndex = xlAutomatic
.Color = 192
.TintAndShade = 0
End With
Selection. FormatConditions (1).StopIfTrue = False
Next i
End Sub

The moderator: we Learn to use tags of design of the code - FAQ
In advance thanks for the help:)

2

Re: Formulas in a cycle

The moderator! We learn to detect institute jobs.

3

Re: Formulas in a cycle

Antonariy wrote:

the Moderator! We learn to detect institute jobs.

the Person resulted the efforts.

4

Re: Formulas in a cycle

Dinre;
In an example you search for the last column on 1 line, however, on a condition, you do not know the size of the table. I.e. you also need to learn the last line and already double cycle For (For i = 1 To kol_stolb and For j = 1 To kol_strok) to search for minimum and maximum values in columns. At me such idea.

5

Re: Formulas in a cycle

Dinre;
On each column it is not necessary to create the rule. It is possible to use the relative reference then the formula works for all columns:

 = (A:A) =A1 

But if it to apply to an empty column, it will be painted over all. To eliminate such behavior, it is possible to color only cells and numbers:

 = ( (A1); MAX (A:A) =A1) 

And this formula can be applied to all sheet. After record of a macro and small "combing" it turns out

Sub Makros1 ()
Range ("A1").Select
With Cells. FormatConditions. Add (Type: = xlExpression, Formula1: = _
"= ( (A1); A1= (A:A))")
With.Interior
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
End With
.StopIfTrue = True
End With
With Cells. FormatConditions. Add (Type: = xlExpression, Formula1: = _
"= ( (A1); A1= (A:A))")
With.Interior
.PatternColorIndex = xlAutomatic
.Color = 192
.TintAndShade = 0
End With
.StopIfTrue = True
End With
End Sub

6

Re: Formulas in a cycle

Dinre wrote:

the Task such: it is necessary to paint over the maximum and minimum value in each column of the table.

Conditional formatting in the help.

7

Re: Formulas in a cycle

For example, so:

Dim OneColumn As Range
For Each OneColumn In ActiveSheet. UsedRange. Columns
OneColumn. Select
Selection. FormatConditions. AddTop10
Selection. FormatConditions (Selection. FormatConditions. Count).SetFirstPriority
With Selection. FormatConditions (1)
.TopBottom = xlTop10Top
.Rank = 1
.Percent = False
End With
With Selection. FormatConditions (1).Font
.Color = vbBlue
.TintAndShade = 0
End With
Selection. FormatConditions (1).StopIfTrue = False
Selection. FormatConditions. AddTop10
Selection. FormatConditions (Selection. FormatConditions. Count).SetFirstPriority
With Selection. FormatConditions (1)
.TopBottom = xlTop10Bottom
.Rank = 1
.Percent = False
End With
With Selection. FormatConditions (1).Font
.Color = vbRed
.TintAndShade = 0
End With
Selection. FormatConditions (1).StopIfTrue = False
Next
ActiveSheet. Range ("A1").Select