Conditional formatting in Excel provides visual cues to help you quickly make sense of your data. For example, it’ll clearly show highs and lows, or other data trends based on criteria you provide. Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. When you mix date functions with conditional formatting, you can create spreadsheets that display date alerts automatically when a deadline is near or differentiate between types of days, like weekends and weekdays.
-->Symptoms
Consider the following scenario.
- You select a cell in a worksheet in Microsoft Excel 2000 or in a later version of Excel.
- You use Microsoft Visual Basic for Applications (VBA) to create a formula-based conditional formatting program.
- You use relative cell references in the VBA conditional formatting program.
- You apply the conditional formatting to a cell other than the selected cell.
When you apply the conditional formatting, you notice that the conditional formatting is not set correctly.
For example, you experience this problem when you use a program that includes VBA code in an Excel worksheet that is similar to the following code:
It's time for you to fight for our ancestral right to Christmas Omelettes. Intergalactic Christmas Chickens are invading Earth, seeking revenge for the oppression of their earthly brethren! Will you succeed? Chicken invaders christmas free online. Will the future of chicken burgers be secure?
This code automatically applies the conditional formatting to cell B1 when you enter '1' in cell A1. When you enter '1' in cell A1, you expect the color of cell B1 to change to red. However, the color of the cell does not change. The color of cell B1 changes to red only if you enter '1' in cell B1.
Additionally, the Conditional Formatting dialog box displays the formula as =B1=1 instead of =A1=1.
Workaround
To work around this issue, use one of the following methods.
Method 1: Use absolute cell references
You can use absolute cell references to refer to the cell that contains the formula instead of to refer to relative cell references.
For example, you can modify the Formula1:='=A1=1' text entry in the VBA code that is described in the 'Symptoms' section as Formula1:='=$A$1=1' to make the code use absolute cell references. This modified version of the VBA code is as follows:
Method 2: Select the cell that you want to use for conditional formatting before you apply the formula
When you want to apply conditional formatting to a cell, first select the cell that you want to use for the conditional formatting. Then, select the cell that you want to use for the formula. After you select this cell, modify the formula to suit your requirements.
Conditional Formatting In Ms Excel 2013
To do this, follow these steps, as appropriate for the version of Excel that you are running.
Microsoft Office Excel 2007
- Start Excel, and then open a new Excel worksheet.
- In the Excel worksheet, select cell B1.
- Click the Home tab.
- Click Conditional Formatting in the Styles group, and then click New Rule.
- Click Use a formula to determine which cells to format under Select a Rule Type.
- Click inside the Format values where this formula is true box. Then, select the cell that you want to use for the conditional formatting.
- Modify the value in step 6 to be =$A$1=1.
- Click Format.
- In the Format Cells dialog box, click the Fill tab.
- Click the color 'red,' and then click OK.
- In the New Formatting Rule dialog box, click OK.
- In cell A1, type 1, and then press ENTER.
- Verify that the color of the cell B1 changed to red.
- Close the Excel worksheet.
Microsoft Office Excel 2003 and earlier versions of Excel
- Start Excel, and then open a new Excel worksheet.
- In the Excel worksheet, select cell B1.
- On the Format menu, click Conditional Formatting.
- Under Condition 1, click Formula Is in the list.
- Click inside the data entry box. Then, select the cell that you want to use for the conditional formatting.
- Modify the value in the data entry box to be =$A$1=1, and then click OK.
- Click Format.
- In the Format Cells dialog box, click the Patterns tab.
- Select the color 'red,' and then click OK.
- In the Conditional Formatting dialog box, click OK.
- In cell A1, type 1, and then press ENTER.
- Verify that the color of the cell B1 changed to red.
- Close the Excel worksheet.
Hi Thenga,
My name is Eric, I have read whole thread carefully and I want to share with you my idea.
Because in column D, we need do conditional formatting base on a formula, Based on formula is more complex then base on a value in the cell for Excel, at present, We can't set three color base on formula at once but set them one by one.
I know you want to optimize the calculation, because 3-color format style only judge by checking the list one time while the three formulas will check the list three times. A s far as I known, it won't cause delay unless you have a huge database, I always assign more than ten formula format in one sheet and the function works well.
Additional, it is possible to use VBA to achieve our goal, because we can link the color of different cells by the Macros (for example link C1 to A1):
Private
Sub
Worksheet_SelectionChange(
ByVal
Target
As
Range)
Me
.Range(
'C1'
).Interior.Color =
Me
.Range(
'A1'
).Interior.Color
End
Sub
If you are interest in it, you can post a new thread in Programming category to get further help about linking color of two different columns.
Garmin apps download. Submitted on 4/19/2018 Review title of DeborahNever againThe service lost connectivity with my windows phone.
Regards,
Conditional Formatting In Ms Excel 2010
Eric
Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.