Set a row color 10 based on the cell contents

These are incorrect, if you use conditional formatting, you can only change the color of a cell, not the color of the row, so to achieve this effect it is best to use VBA to achieve the best, first open the VBA machine, and then insert the module, copy the following **.

sub aaa()

dim i, lastrow as integerlastrow = [e65536].end(3).rowfor i = 1 to lastrow

select case trim(cells(i, 5).value)

case "cancelled"

rows(i). = 10 'Green.

case "active"

rows(i). = 6 'Yellow.

end select

nextend sub

Then run the macro, the specific effect is shown in the figure.

<> you select the range of fill colors and click on the button in the red box

You see a small black arrow on the bottom right side of the small icon in the red box, and the color palette will appear when you click on the arrow, change the color you like!

Then click 45 with the left mouse button and choose the color you want.

<> the description is not clear, and it is completely guessed.

Taking the cells in column A or laptop as an example, suppose 1 turns yellow and 2 turns green.

Select A1, and then Conditional Grid Formula == >> New Rule == >> Use Formula ==

Reference formula =$a 1=2

Set the settings, then set the corresponding color, and then set the color when the other conditions are set. After the setting is complete, you can brush the cell to the desired area.

If the "different value" means that the value is different from other data, you can modify the conditional formatting to:

countif($a$1:$a$15,$a 1)=1, where the range is modified as appropriate.

Note that the $ in the formula cannot be adjusted indiscriminately.

1. How to use conditional formatting

Excel has a very useful function, [Conditional Formatting], which is used to color cells that meet the conditions.

For example, the following is a commonly used track of the progress of the problem**, and the third column identifies the current status of each problem.

There's nothing wrong with it, it's just not intuitive, for example, there's a [delayed] job in it, haven't you noticed it?

We just need to select the third column and click on the toolbar "Conditional Formatting" "Highlight Cell Rules" equals.

Then enter "delayed" in the pop-up dialog box to confirm.

We got the results we wanted, isn't the table below significantly more? Delayed work can no longer fish in troubled waters.

In the same way, mark the other states, and try it. For example, the one marked with the "Complete" status:

Conditional formatting can also handle greater than or equal to, or even the processing of various formulas, such as those commonly used by class teachers, marking grades less than 60 points. Xiao Ming, come here!

2. Conditional formatting is applied to the entire line

Here's the main course for today.

I don't know if you have encountered the following **, an excel vast column, my example is quite few, many excel still need to drag the horizontal scroll bar back and forth to show the end.

Lao Hu is deeply disgusted by such **, although we have identified the color of the state above, but because there are too many columns, when I want to see the status of the 6th line of excel, I have to use the mouse to select this row, and then look at its state from the right, so it's too low! I'm blind, okay?

1. Select the column of conditional formatting we just made, and click "Conditional Formatting" Manage Rules.

2. There is a ** in the pop-up dialog box, take the first line to explain.

The first column is the rule, here the cell value="done".

The second column is the format, here is the light green background + dark green text.

The third column is applied to those ranges, and here it is applied to column k.

What we need to do is that when a row in column K is equal to "complete", the whole row is green. The first and third columns need to be modified. The third column is simply changed to = l, which means that it should be applied to column A to column L.

Then double-click on the first column.

3. In the pop-up dialog box, you need to do three steps, which means that when the K column is equal to the completion, the color is green.

The modified style is as follows:

Click OK to see the effect:

There are many other ways to use conditional formatting, so let's get a feel for it.

Assuming the data is in row 2, you need to change the fill color of the whole row according to the value change of cell C2.

Select the row to be set, create a rule in Conditional Formatting, create a rule with Formula, and enter it.

c2>=10 format is set to fill color yellow, OK;

At the same time, select the row to be set, and in Conditional Formatting, create a rule, create a rule with Formula, and enter it.

C2<10 is formatted to fill color green, OK.

1.Choose a cell and set up conditional formatting.

2.Add the criteria you want to select.

3.Click Format to set the changed format.

4.You can fill other cells with fill handles, or you can copy them and paste them specially.

The specific steps are as follows:

The materials that need to be prepared are: computer, excel**.

1. First open the required excel**, click to open the "Highlight Cell Rules" in the conditional formatting.

2. Then click to open "Equals" in the pop-up window.

3. Then in the pop-up window, in the pop-up window, in the format of the cells equal to the following values, enter "=" and then select the cell where you want the specific text to be located.

4. Then click to open "Set as" and select "Custom format".

5. Select the desired fill color and press enter to confirm.

1. In this codex, I want to display blue in the remarks column when I enter the channel, and it is red when I enter the pass.

2. First, use the mouse to select all the target columns to fill the color.

3. Drop down the "Conditional Formatting" section of the toolbar and click "New Rule".

4. Click to select the second rule type.

5. Drop down the cell value and click "Specific Text".

6. Enter "qualified" in the text box to the right of "contains".

7. Then click "Format" on the "Preview" side.

8. In the pop-up dialog box, click "Font", then select the lower color blue, select it and click "OK" below.

9. Then click the OK button below the new rule.

10. Similarly, when the input in the cell is unqualified, set the font to red so that ** can be set.

Select your ** area, e.g. a1:h100, format - conditional formatting - use formulas to set the cells to be determined.

Input=$a 1="Beijing City"

Click "Format" and set "Fill" to red, OK. ok！

Select all (or select the row you want to judge), open the menu "Format" "Conditional Formatting" "Condition 1" - "Formula", and enter:

a1="Beijing City"

Click the "Format" button next to it to select the red border and background, and save it.

(1) Select the entire worksheet.

2) Execute "Format" and "Conditional Formatting" in the menu bar, select "Formula", and enter the formula: =$a 1="Beijing City"

3) Click "Format" in the dialog box, set "Pattern" and "Cell Shading" to red, "Border" to a certain line, and the color to red.

4) Click "OK" and "OK".

Find out and learn the relevant commands, these can be searched on the Internet.

Take a look at conditional formatting, this feature is there to meet your requirements.

Select All and then select the conditional formatting in New Rule to determine the cells you want to design formatting with a formula, in the format.

For example, offset($b$1,row()-1,0,1,1)="Fail"

Modify the location in $b by yourself"Fail"Modify the fill color to the text you want.

Excel click on a cell, a row of the cell becomes the specified color, in Office Excel needs VBA programming implementation, and WPS** has this function, the specific operation is as follows:

Click the drop-down arrow for Reading Mode on the Status Bar;

Select a color in the pop-up color selection box.

Renderings of WPS reading mode.

It is implemented by VBA programming in Excel.

Create a worksheet event directly from the worksheet you are in:

VBA events.

private sub worksheet_selectionchange(byval target as range)

xlnone

end sub

Analysis: Private Sub Worksheet SelectionChange(Byval Target as Range), which refers to the following set of actions when the selected cell is changed.

XLNONE is to clear all colors in the worksheet = 65535 first, and to change the entire row of the selected cell into the color 65535, which can be changed to the corresponding color according to your needs**.

In this way, when you change the cell, the row of the selected cell will change to the specified color.

private sub worksheet_selectionchange(byval target as range)

rows( = 5287936

end sub

Turn green** put there, refer to below.

Excel selects the data of the cells in the current table to jump to the same cells in another table.