Marcro Excel compare cells

[malpa]

Hi,

I have this kind of file .xls

switch |h1| hT1| n2| nT2| f3| fT3| h51| hT5| ..... h| hT| f| ft| n| nT

item1 . . . . . . . . ..... . . . . . .
item2 . . . . . . . . ..... . . . . . .
item3 . . . . . . . . ..... . . . . . .
item4 . . . . . . . . ..... . . . . . .

..
For each register, I want to compare the value of h, hT with the value h1, ht1 and the value h, ht with the value of h5, hT5 respectively, if there is a reduction of 10%, the macro must to fill up with red colour. If there is a increase of 10% the macro must to fill up with blue colour.

The same with f, fT and n, nT.

The columns is not in order |h1| hT1| f2| fT2| n3| nT3| h51| hT5| with respect to the columns h| hT| f| ft| n| nT
because the order of |h1| hT1| f2| fT2| n3| nT3| h51| hT5| can change


I would like to attach the file, but I don`t know how.

Thanks a lot.

 

[mintjulep]

Seems like you could do this with conditional formatting. No macro required.

 

[Gavona]

1. You may run into uneccessary problems because your column headings are too like cell references. Suggest you change them e.g. by adding a prefix of "My".

2. Use Ctrl-A to Select the entire database.

3. Insert,Name,Create to create a named range for each columns data based on the column heading.

4. Create conditional format. My sample included two conditions in the style
Formula Is =E6>Myh1*1.1
This is in cell E6 with cell E1 having the value Myh

5. Repeat using the Macro Recorder. This gives a solution if column positions are fixed... but

the order of ...[columns]... can change

Several alternative solutions to this aspect.
a. Use the Indirect function within the conditional format to construct the comparison column (rangename). =C5>INDIRECT(C$1&1)*1.1

b. Use Macro to re-order the columns

c. Use Edit,Find to find specific columns to which you apply the conditional formats.

d. Insert rows above the data (with a blank row in between) to specify the comparison column for that data. Use vlookup to populate this row. Refer to this row in your conditional format using the Indirect Function.

Good luck. Post back with any problems including what you have tried.




Gavin

 

[Gavona]

Apologies. My solution does not work. Specifically steps 3, 4, 5a

I cannot get either Labels or NamedRanges to work in the desired way within conditional Format... Got to mow the lawn now so hopefully someone else will think of a solution!

Deleting all the named ranges and using =Myh1*1.1 in a spare column multiplies the value in the same row in the Myh1 column by 1.1. However in a conditional format it appears to base the calculation on the first value in the Myh1 column, irrespective of what row the conditional format is on.
(To use labels in formulae first go to Tools, Options, Calculation and enable the feature)

Gavin

 

[Gavona]

Sorry about posting an inadequately tested solution earlier.

If all columns are present but in different order then Data,Sort, Options Left-to-right.

This should leave each column adjacent to the column you want to compare it with.

(You could first add a row with ascending numbers in it so that you can re-sort at the end back to the original order)

In column B set a conditional format refering to column A. Copy format to columns D,F,H........

Without the sorting this should get you started:

Sub Test3()
'Assumes B6 contains the first data to be compared with _
that in the previous column.  ie Row 5 contains the column headings
'Assumes that data is surrounded by blank rows and columns

Dim c As Range, MyRowCount As Integer
Set c = Range("B6")

[red]c.Select  'why do I need this?  Conditional format seems to be affected by current selection![/red]

MyRowCount = c.CurrentRegion.Rows.Count - 1
With Range(c, c.Offset(MyRowCount, 0))
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=A6 * 0.85"
    .FormatConditions(1).Interior.ColorIndex = 4
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=A6 * 1.1"
    .FormatConditions(2).Interior.ColorIndex = 3
End With
    Columns("B:B").Copy
    Columns("D:D").PasteSpecial _
        Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Columns("F:F").PasteSpecial _
        Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Columns("H:H").PasteSpecial _
        Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Columns("J:J").PasteSpecial _
        Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Gavin

 

[malpa]

Thanks a lot for your reply Gavona.

I was working on it. Down, my little program, but it spends much time. With this I try to explain my little proyect. I make the file that contains the data with awk. Now what I want in excel application is to fill the cells with colors depends of the day and if is increased or decresed a 10% with respect to the average value.

Is It possible to do this more efficient?

(2,4) 125 128
switch |h1| hT1| n2| nT2| f3| fT3| h51| hT5| ..... h| hT| f| ft| n| nT

item1 . . . . . . . . ..... . . . . . .
item2 . . . . . . . . ..... . . . . . .
item3 . . . . . . . . ..... . . . . . .
item4 . . . . . . . . ..... . . . . . .

item 2060

' H means capable day
' N means non capable day
' F means holiday
' S means Saturday

' HCp average calls capable day
' HSp average seconds capable day
' NCp average calls non capable day
' NSp average seconds non capable day
' FCp average calls holiday
' FSp average seconds holiday
' SCp average calls saturday
' SSp average secons holiday



Sub Color()
'
'
'
'

Dim var As String
Dim i, j, k As Integer

i = 2 ' row head
j = 4 ' column head

var = InputBox("Input your opcion H N F S T")

MsgBox ("the value is " & var)


Select Case var

Case "H"


While (i <= 2060) ' Rows It value can be change I would like to do this dynamically

A:
While (j <= 125) ' Columns , March 1 Calls, March 1 Seconds .......................May 1 Calls, May 1 Seconds. It value can be change I would like to do this dynamically
x = 0
While (x <= 7) ' Columns HCp HSp NCp NSp FCp FSp SCp SSp averages


If (Cells(2, j) Like "Calls*H*") And (Cells(2, 128 + x) Like "*HCp*") Then ' head


If Cells(i + 1, j) = 0 Then
Cells(i + 1, j).Interior.ColorIndex = 3 ' values
j = j + 1
GoTo A:
End If

'If Cells(i + 1, j) < Cells(i + 1, 128 + x) Then
If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) > 10) Then
Cells(i + 1, j).Interior.ColorIndex = 46
j = j + 1
GoTo A:
End If

If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) < -10) Then
Cells(i + 1, j).Interior.ColorIndex = 50
j = j + 1
GoTo A:
End If


End If


If (Cells(2, j) Like "Minutes*H*") And (Cells(2, 128 + x) Like "*HSp*") Then

'Cells(i + 1, j) < Cells(i + 1, 128 + x)

If Cells(i + 1, j) = 0 Then
Cells(i + 1, j).Interior.ColorIndex = 3
j = j + 1
GoTo A:
End If

If Cells(i + 1, 128 + x) = 0 Then
Cells(i + 1, 128 + x).Interior.ColorIndex = 3
j = j + 1
GoTo A:
End If

'If Cells(i + 1, j) < Cells(i + 1, 128 + x) Then
If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) > 10) Then
Cells(i + 1, j).Interior.ColorIndex = 46
j = j + 1
GoTo A:
End If

If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) < -10) Then
Cells(i + 1, j).Interior.ColorIndex = 50
j = j + 1
GoTo A:
End If

End If



x = x + 1

Wend

j = j + 1

Wend
j = 4

i = i + 1

Wend

Case "N", "n"

While (i <= 2060)

B:
While (j <= 125)
x = 0
While (x <= 7)

If (Cells(2, j) Like "Calls*N*") And (Cells(2, 128 + x) Like "*NCp*") Then

'Cells(i + 1, j) < Cells(i + 1, 128 + x)

If Cells(i + 1, j) = 0 Then
Cells(i + 1, j).Interior.ColorIndex = 3
j = j + 1
GoTo B:
End If

'If Cells(i + 1, j) < Cells(i + 1, 128 + x) Then
If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) > 10) Then
Cells(i + 1, j).Interior.ColorIndex = 46
j = j + 1
GoTo B:
End If

If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) < -10) Then
Cells(i + 1, j).Interior.ColorIndex = 50
j = j + 1
GoTo B:
End If


End If

x = x + 1

Wend

j = j + 1

Wend
j = 4

i = i + 1

Wend

Case "F", "f"




While (i <= 2060)

C:
While (j <= 125)
x = 0
While (x <= 7)

If (Cells(2, j) Like "Calls*F*") And (Cells(2, 128 + x) Like "*FCp*") Then

'Cells(i + 1, j) < Cells(i + 1, 128 + x)

If Cells(i + 1, j) = 0 Then
Cells(i + 1, j).Interior.ColorIndex = 3
j = j + 1
GoTo C:
End If

'If Cells(i + 1, j) < Cells(i + 1, 128 + x) Then
If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) > 10) Then
Cells(i + 1, j).Interior.ColorIndex = 46
j = j + 1
GoTo C:
End If

If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) < -10) Then
Cells(i + 1, j).Interior.ColorIndex = 50
j = j + 1
GoTo C:
End If


End If

x = x + 1

Wend

j = j + 1

Wend
j = 4

i = i + 1

Wend

Case "S", "s"




While (i <= 2060)

D:
While (j <= 125)
x = 0
While (x <= 7)

If (Cells(2, j) Like "Calls*S*") And (Cells(2, 128 + x) Like "*SCp*") Then

'Cells(i + 1, j) < Cells(i + 1, 128 + x)

If Cells(i + 1, j) = 0 Then
Cells(i + 1, j).Interior.ColorIndex = 3
j = j + 1
GoTo D:
End If

'If Cells(i + 1, j) < Cells(i + 1, 128 + x) Then
If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) > 10) Then
Cells(i + 1, j).Interior.ColorIndex = 46
j = j + 1
GoTo D:
End If

If (100 - (Cells(i + 1, j) / Cells(i + 1, 128 + x) * 100) < -10) Then
Cells(i + 1, j).Interior.ColorIndex = 50
j = j + 1
GoTo D:
End If


End If

x = x + 1

Wend

j = j + 1

Wend
j = 4

i = i + 1

Wend


End Select

End Sub


Thanks malpa

 

[Gavona]

I do not think that I have fully understood the structure of your data or what you are trying to achieve but leaving that to one side for now:

Your code is always going to be MUCH slower than if you use built in Excel functionality. I still think therefore that you are best achieving the colour fill using conditional formats. You can use code, as I did in my last post, to apply the conditional formats to the relevant cells.

Forget VBA for a moment - try to do it manually the first time.
1. There is a column in your data containing the data for h and another containing the values for h1.
You want to colour the cell in h1 if it varies by more than 10% from h.

2. I believe that your headings are in row 2 but your data starts on row 4. Row 3 is blank. If this is wrong then you will need to adjust my instructions accordingly.

3. I will assume in what follows that h is in column B and h1 is in column C. You will need to adjust accordingly.

4. Select Cell C4

5. From the menu choose: Format, Conditional Format
Select "Cell Value Is" from the first drop down.
In the next box select "Less Than"
In the third box enter =B4*0.9
Click on the Format button and select the format to use if the above condition is true

Click ADD to add a second condition
Select "Cell Value Is" from the first drop down.
In the next box select "Greater Than"
In the third box enter =B4*1.1
Click on the Format button and select the format to use if the above condition is true

Click OK to close the conditional format window

6. Play with the value in cell B4 to confirm that the colour of C4 is changing as you require.

7. Copy the format from cell C4 to the other cells in column C. Post if you don't know how to do this (either use the format painter or PasteSpecial, Formats)

8. All the cells in this column should now be the colour you require.

If the above has worked then read my last post...and post back.

Other Thoughts:
It is extremely helpful to structure your data the way Excel likes. Your data should be surrounded by blank rows and columns (or the edge of the worksheet) but should not contain any totally blank rows or columns. For example you should delete the totally blank row between your column headings and your data.







Gavin

 

[malpa]

Hi

Thanks for the replays.

Attach example of the file .xls. this example only contain three days. The original file contain 60 days. There are not spaces between colummns or rows.


That I want is compare the columns

' HCp average calls laboral day
' HSp average minutes laboral day
' NCp average calls non laboral day
' NSp average minutes non laboral day
' FCp average calls holiday
' FSp average minutes holiday
' SCp average calls saturday
' SSp average minutes saturday

with the ( Calls 20080401 Tue H;Minutes 20080401 Tue H;Calls 20080402 Wed H;Minutes 20080402 Wed H;Calls 20080604 Wed H;Minutes 20080604 Wed H ) columns


where H means laboral day, N means non laboral day, F means holiday and S means Saturday.


For each register, I want to compare the value of call "day H", minutes "day H" with the value HCp, HSp respectively, if there is a reduction of 10%, the macro must to fill up with red colour. If there is a increase of 10% the macro must to fill up with blue colour.

The same for the rest of month, where you can to find non laboral days, holidays and saturday days.

I´m sorry my English is not good.

Switch;code;service;Calls 20080401 Tue H;Minutes 20080401 Tue H;Calls 20080402 Wed H;Minutes 20080402 Wed H;Calls 20080604 Wed H;Minutes 20080604 Wed H;total_Calls;total_Minutes;HCp;HSp;NCp;NSp;FCp;FSp;SCp;SSs
switch1;794;11111111;4096;10173;4087;9511;0;0;194921;503623;3803;9264;1584;5685;1456;4873;2098;5902
switch2;460;77777777;0;0;0;0;0;0;1;13;1;13;0;0;0;0;0;0
switch3;632;22222222;0;0;0;0;0;0;59;42;2;1;1;0;0;0;1;1
switch4;1004;99999999;8094;17231;8764;19780;0;0;678422;1479802;10736;23516;9448;20998;7584;16545;12456;26326
switch5;807;44444444;43;30;33;36;0;0;2085;1887;32;30;21;21;19;20;59;43
switch6;929;33333333;4506;11846;4518;10858;0;0;327437;773837;5814;13323;4152;12070;3803;9615;4933;11349
switch7;978;33333333;1517;782;1558;709;0;0;71440;38484;1513;842;567;248;624;265;939;461
switch8;493;66666666;57;1527;56;1768;0;0;3047;84884;51;1488;33;1028;35;756;46;1119
switch9;666;00000000;4539;8081;4464;8426;0;0;247840;438412;4603;8038;2273;4548;1869;3597;3463;5946
switch10;666;88888888;541;788;527;925;0;0;26976;43626;513;821;203;364;187;300;370;607


Thanks malpa

 

[Gavona]

OK:
I assume when you say "for each register" you mean "for each switch"?

Have you tried using conditional formats? Using the approach I described I get the following results for the first data column:
Switch code service Calls 20080401 Tue H
switch1 794 11111111 4096
switch2 460 77777777 [red]0[/red]
switch3 632 22222222 [red]0[/red]
switch4 1004 99999999 [red]8094[/red]
switch5 807 44444444 [blue]43[/Blue]
switch6 929 33333333 4506
switch7 978 33333333 1517
switch8 493 66666666 [red]57[/Red]
switch9 666 0 4539
switch10 666 88888888 541

So my questions to you:
Q1: Have I understood your requirements? Are the cells above the correct colours?
Q2: Have you tried achieving the results you require using conditional formats? If not then why not? Do you need more help with this feature of Excel?

Gavin

 

[Gavona]

4506 should show as red too - sorry I missed that.

Step 1: Construct conditional formats for "Calls 20080401 Tue H"

Step 2: Adjust the conditional formulae to make the reference to column [HCp] absolute e.g. =$L3*0.9

Step 3: Copy the formats of the column to every other column where the title starts "Calls"

Step 4: Switch macro recorder on. Record yourself repeating the above 3 steps for column "Minutes 20080401 Tue H"

Step 5: Record yourself selecting the row containing your titles and using Edit, Find to find the next cell containing "Calls". This may be useful as you say that the columns come out in different orders.

Step 7: Examine your code and endeavour to (i) simplify/generalise it and then to automate your process.

Good luck. Post back when you need a bit of guidance.

Gavin