Run the updated macro and observe that all the negative valued cells are now filled with the color red.VBA enables you to use English like statements to write instructions for creating various applications.
#Visual basic for excel update#
Update the code with the following modification. To add the third possible action will require the addition of an ELSEIF statement directly after the initial IF statement. The color code for the red we will be using is 192. We will now update the code to color all the negative valued cells red. Task #4 – Negative values will be colored red Run the updated code and notice how all the previously white cells are now yellow. To add the second possible action will require the addition of an ELSE statement at the end of our existing IF statement. The color code for the yellow we will be using is 6740479. Now that we have identified all the numbers between 1 and 400, let’s color the remaining cells yellow. If Range("B9").Value > 0 And Range("B9").Value 400 or 0 And cell.Value 400 yellow Update the code with the following IF statement. We will use an AND statement to allow the IF to perform multiple tests. Suppose we want to test the values in Column B to see if they are between 1 and 400. If we change the value in cell B9 to -2, clear the contents of cell C9 and re-run the macro, cell C9 will remain blank. Click in the code and press F5 or click the Run button on the toolbar at the top of the VBA Editor window. Test the function by executing the macro. When using this line-break style, don’t forget to include the END IF statement at the end of the logic. If you have more than one action to perform, you will want to break your code into multiple lines like the following example. If you only have a single action to perform, you can leave all the code on a single line and you do not have to close the statement with an END IF. If Range("B9").Value > 0 Then Range("C9").Value = Range("B9").Value In the Code window, click between the Sub and End Sub commands and enter the following. If the value is >0, we will display the value of cell B9 in cell C9. We want to evaluate the contents of cell B9 to determine if the value is greater than 0 (zero). In the Code window (right panel) type the following and press ENTER. Right-click “This Workbook” in the Project Explorer (upper-left of VBA Editor) and select Insert ⇒ Module. In Excel, open the VBA Editor by pressing F-11 (or press the Visual Basic button on the Developer ribbon.) Once we have the logic correct, we will apply the logic to a range of cells using a looping structure. In this example we will evaluate a single cell.
Similarly, you can ask several questions and if any single or multiple of questions are true, the action will be performed. Like the AND function, you can ask several questions and all the questions must evaluate to TRUE to perform the action. The IF…THEN can also evaluate many conditions. Another test could be to test the value of a cell, such as “Is the cell value greater than 100?” If so, display the message “Great sale!” Otherwise, display the message “Better luck next time.” You give the IF a condition to test, such as “Is the customer a “preferred” customer?” If the customer is classified as “preferred” then calculate a discount amount. The IF…THEN statement is like the IF function in Excel. The IF…THEN statement allows you to build logical thinking inside your macro. The IF…THEN statement is one of the most commonly used and most useful statements in VBA.
0 kommentar(er)
