Hi i neeed some help im trying to do an if statement in excel but i need to make if so it recognises it in every cell in column BO and BP without having to type in the code for every single option for example if bo2 = yes then bp2 = no ive got this but it doesnt work for every cell Private Sub CommandButton1_Click() If Range("bo2") = "yes" Then Range("bp2") = "nope" Else If Range("bo2") = "lion" Then Range("bp2") = "tiger" End If End If If Range("bo3") = "no" Then Range("bp3") = "yh" End If End Sub please help anyone ?

This code should do what you want. It uses a for ... next loop and also uses a select case statement. Both are really handy for doing repetitive tasks! Note I've used LCase() for when I'm writing the variable to make it work for "Lion", "LION", "lioN" etc. sub loopthrough() for i = 2 to 100 '100 is the last row vartest = LCase(sheet1.cells(i, 67).value) Select case vartest case "lion" varresult = "Tiger" case "yes" varresult = "no" case "another thing" varresult = "good" case else varresult = "unknown" end select Sheet1.cells(i, 68).formula = varresult Next i end sub

Thanxs for this how does it know which cell to put it in ? can this be activated when a button is pressed

cells(i, 67) is a way of writing "BO"i (where i is a number between 2 and 100) cells(i, 68) is therefore a way of writing "BP"i (where i is a number between 2 and 100) Yes it can be assigned to a button. On excel 2003, go to Tools/Customise... On the "Commands" tab, go down to macros, drag "custom button" up to the toolbar. Click on it once you've closed that, and you can assign the macro to it. It won't work in another workbook though. In fact I can't remember how to make it appear in only one workbook. I never use them, prefer to use Alt-F8. DerbyDad will know I'm pretty sure!

Thankyou for the help i think i can edit it to what i want now :) thanx