Blog of Calc LLC

Suppose you have a table of students scores for the math exam. Also assume the maximum possible score is 100. Therefore, any value above 100 is an error. And you want to highlight the error values in red. See the data below. The VBA code is this: 'If a score>100, it is an error. Mark it in red. Sub Change_color()     Dim row As Integer     Dim score As Integer     For row = 2 To 16         score = Cells(row, 2)         If score > 100 Then             Cells(row, 2).Font.Color = RGB(255, 0, 0)         End If     Next     MsgBox ("done") End Sub The RGB() function is the function that Excel uses to set colors. RGB(255, 0, 0) is for red. By running the code, you get the maximum value:

You can format Excel cells based on their values, which is called conditional formatting. Suppose we have the scores of 5 students in each subject. 1. Click Home >  Conditional Formatting. 2. Select Highlight Cells Rules > Text that Contains, a window pops up. We want to format cells that contains the text "A" in a special format, so we fill "A", and click OK. 3. Now the cells with "A" have light red fill with dark red text. 4. If you want to remove the formatting, you can use clear format

You can easily format raw Excel data as a nicely looking table, using the "Format as Table" tool. Suppose the following is our raw data, which shows scores of 5 students in each subject. 1. Click Home > Format as Table , and select your favorite style. 2. Select the data range. Remember to check " My table has headers " since our data does have headers. 3. Click OK , and you see a nicely looking table. It not only has alternate colors in rows, but also has header filters, which you can use to filter data. 4. Click on the "Math" column, you will see the available values.