|create one rule includes all my companies' names|
What you might try is using a Defined Name range with your Conditional Formatting.
WARNING, This will only work, if the two character strings you are trying to match are exactly the same.
So something like: Amiga Corporation is NOT the same as Amiga Corp.
Also, to Excel a Space is a legitimate charcter, so Amiga<space>Corporation is NOT the same as Amiga<space><space>Corporation even though they may appear the same.
First define your Name range:
List all of your company holdings in a column, as an example I will use column X
Select all the cells with data in column X, IE X1 - X300
On the ribbon, select the Formula Tab
In the Define Names section, Select Define Name
In the Name box enter a name for you selection, like NameList
In the Scope box, leave it at Workbook
In the Refers to box, it should show the range of cells you
selected, IE =sheet1!$X$1:$X$300
Second define your Conditional Formatting
I do not know what column you will be checking against,
so I will use column A as an example.
1) Select your cell or range of cells, IE A1-A2000
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:
6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK
Now, any name in column A that matches your company holdings in column X will be highlighted in your selected color.
message edited by mmcconaghy