Thank you so much..it worked. Another question..is there a shortcut to apply this formula to column or rows without copy and pesting? I have like 20 rows and 6 columns..:(
Lets say you want to apply this rule to column A and B fully
then click of Cell A1 first
then select column A and B
go to conditional format and apply the formula for A1 cell
It will copy for next rows on its own. Basic idea is that first you select the first cell of the range, then select your range and finally you apply the format condition for that row or column (depends on what you want) but the first cell of the range
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com,http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.
Note: your data need not be to be real data but a good representative of how data looks like
Refer to cells E10 to K10 and M10 (exclude column L)..I have conditional formatting. I need to do that for the entire table. Second table below is where we are comparing the values by combination of column C and D. Hope this helps.
From what I understood, you want to compare the upper table with bottom. since the rows do not follow same sequence in both tables, you would need to apply conditions on group of rows (like row 10, 11 and 12 can be done in one go
1. click on cell E10
2. Select from E10 till K12
3. Goto conditional format and apply formula
=AND(E10<>E38,E10<>" - ")
4. Click on Cell M10
5. Select from M10 till M12
6. Goto conditional format and apply formula
=AND(M10<>L38,M10<>" - ")
7. click on cell E13
8. Select from E13 till K13
9. Goto conditional format and apply formula
=AND(E13<>E42,E13<>" - ")
10. Click on Cell M13
11. Goto conditional format and apply formula
=AND(M13<>L42,M13<>" - ")
The conditional formatting in cell D7 is the Rule "=ISBLANK(G7)" and the Applies To range is $D$7:$D$60 (removing the absolute references does no good since when you click APPLY, Excel 2007 puts them right back). Either filling, Copy Pasting, or Format Painting produces the same result. In cells D8 through D60, the following rule appears, "=ISBLANK(G7)" . I've tried applying the same technique from the second and third rows in this "array" but, wherever I start is the relative start which all of the relatively coded rules, no matter how far down the array, are absolutely referencing.
This isn't the case in Excel 2003; has anyone found an answer for Excel 2007?
Toast, even though you need explain your issue in detail. For reason that I am thick headed, I am not fully grasping it. Could you post a book and re-explain what is the issue
When the CF Rule in D7 references "G7", copying, filling, or Format Painting the same CF in cells D8:D60 produces the same reference to "G7" when I need the CF in D25 to reference G25. Editing the Applies To CF parameter doesn't change anything. And as you can see, I don't need to use "$" to make a relative reference absolute; everything just references the contents of the top row from which the CF is applied, in my case, downward, absolute reference or not.
FOUND IT!
Incremental Format Painting, that's the key. I thought I could tempt fate by doing the failed techniques listed above but try them in R1C1 format. Turns out all you have to do is Format Paint an array one proximal cell at time, in or out of R1C1. Set up cell D7's CF, select cell D7, and FP to D8. Select cell D8, FP to D9; select D9, [ ] and finally, select cell D59, FP to D60.
The conditional formatting in cell D7 is the Rule "=ISBLANK(G7)" and the Applies To range is $D$7:$D$60 (removing the absolute references does no good since when you click APPLY, Excel 2007 puts them right back). Either filling, Copy Pasting, or Format Painting produces the same result. In cells D8 through D60, the following rule appears, "=ISBLANK(G7)" . I've tried applying the same technique from the second and third rows in this "array" but, wherever I start is the relative start which all of the relatively coded rules, no matter how far down the array, are absolutely referencing.
This isn't the case in Excel 2003; has anyone found an answer for Excel 2007?
https://ccm.net/forum/office-software-25/new
When the CF Rule in D7 references "G7", copying, filling, or Format Painting the same CF in cells D8:D60 produces the same reference to "G7" when I need the CF in D25 to reference G25. Editing the Applies To CF parameter doesn't change anything. And as you can see, I don't need to use "$" to make a relative reference absolute; everything just references the contents of the top row from which the CF is applied, in my case, downward, absolute reference or not.
Incremental Format Painting, that's the key. I thought I could tempt fate by doing the failed techniques listed above but try them in R1C1 format. Turns out all you have to do is Format Paint an array one proximal cell at time, in or out of R1C1. Set up cell D7's CF, select cell D7, and FP to D8. Select cell D8, FP to D9; select D9, [ ] and finally, select cell D59, FP to D60.