2010 Conditional Formatting Help

[Ben Flannaghan]

Please can someone advise how I do the following in excel 2010.
I have a row with 10 cells, and 1000 rows altogether.
I would like the whole row to be filled purple if the first cell in the row =2. I can do this individually but dont want to have to do this 1000 times, so tried pasting the format.

when I tried to special paste the format to the other 999 rows I cannot get it to work exactly the same as the range it applies to seems to change.

I'm quite new to excel 2010 so an idiots guide is required

Many thanks

Ben

[dktakyar]

1. Click on the developer tab in the menu bar
2. Click on the drop-down arrow below insert
3. Select the rectangular command button from the ActiveX controls
4. Select a convenient location and then click and drag the mouse button to draw a command button
5. Double click on the command button
6. Between the 2 lines of code that you will see in the Visual Basic Editor (VBE) Window just copy and paste the following code:

Dim i as integer
For i = 1 To 1000
If Cells(i, 1) = 2 Then
Range(Cells(i, 1), Cells(i, 10)).Interior.ColorIndex = 13
Cells(i, 1).Font.ColorIndex = 2
End If
Next i

7. Now close the VBE window and come back to your Excel worksheet
8. Click on the developer tab in the menu bar
9. Click on 'Design' to exit the design mode
10. Now click on the Command Button1
11. The relevant row will be highlighted purple and if the first cells values are 2 then the font of the value 2 will become white

[tvelo]

I have a different problem. I have a column of cells that are greater than, less than or equal to, the cell preceding it. So, for instance, cell AG5 is >, <, = to AF5. In this workbook it is less than. I want to format the cells based on the value of the preceding cell. Red if it is less than, green if it greater than and blue if it is equal to. Is this possible. Thank you. Tom

[dktakyar]

Again insert a command button and give it the caption 'color values'. Now double click on the command button and use the following code to color the values accordingly:
Dim i As Integer
For i = 1 To 3
If Cells(i, 32).Value = Cells(i, 33).Value Then
Cells(i, 32).Font.ColorIndex = 5
ElseIf Cells(i, 32).Value < Cells(i, 33).Value Then
Cells(i, 32).Font.ColorIndex = 3
ElseIf Cells(i, 32).Value > Cells(i, 33).Value Then
Cells(i, 32).Font.ColorIndex = 4
End If
Next i

I've assumed that you have data in 3 rows. If you have more rows of data then change the value of 3 to whatever your requirement is.
To get the color indices visit this website: http://dmcritchie.mvps.org/excel/colors.htm

[tvelo]

Thanks for the help. Now I am a lot closer, but the command is asking me to debug and nothing happens. I could not caption the command to "Color Values" I have attached a screen shot, please help me the rest of the way. Thank you very much, Tom.

[dktakyar]

Have a look at my code again. Instead of ag and af, you should use i.
For changing the caption, right-click on the button - select properties and against the item Caption in the new window (properties) that pops up, just write your caption, eg. color values.

[tvelo]

Thanks for the update D! One more question: if I have 14 rows and subsequent columns such as: ah, ai, aj, ak, etc. do i need to do anything to the code? Thanks for your help, Tom.

[dktakyar]

Just change the for loop to:
for i = 1 to 14
If you have headers in row 1 then change the for loop to:
for i= 2 to 15
You don't need to change anything else.

[tvelo]

dktakyar - thank you for your help, but I am doing something wrong. I modified the code to your instructions, but I get the wrong column changing colors. Please help me one more time. Thank you so very much.

Tom