copy cell color but not conditional format equations

Following is highly abridged code from Bernie Deitrick regarding copying Type 
2 conditional formats.  Column C is formatted yellow if A1>B1, etc.    There 
are 10 rows.  I want to copy the cell color (but not the format equations) to 
column D.  But the line
bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
always returns Bcheck=True, then myRet is set to 1, and every cell in column 
D is colored (even where the condition is not true).  I think I need another 
IF statement to check if the conditional format is true (and the cell is 
colored), but I can’t get anything to work.  TIA
Option Explicit
Dim R1 As Range
Dim R2 As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Sel As Range
Dim myRet As Variant
Dim bCheck As Boolean
Sub CopyCFFormatsA()
Set Sel = Selection
Set R1 = Range("c1:c10")
Set R2 = Range("d1:d10")
j = 1
Application.EnableEvents = False
For i = 1 To R1.Rows.Count
R1.Cells(i, j).Select
myRet = CheckFormat(R1.Cells(i, j))
If myRet = False Then GoTo NoCF
If myRet = "None" Then GoTo NoCF
R2.Cells(i, j).Interior.colorindex = _
R1.Cells(i, j).FormatConditions(myRet).Interior.colorindex
NoCF:
Next i
Sel.Select
Application.EnableEvents = True
End Sub
Function CheckFormat(c As Range) As Variant
CheckFormat = "None"
For k = 1 To c.FormatConditions.Count
bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
    If bCheck Then
        CheckFormat = k
        bCheck = False
        Exit Function
    End If
Next k
CheckFormat = "None"
End Function

-- 
Bill Roberts
0
Utf
12/8/2009 11:26:01 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1653 Views

Similar Articles

[PageSpeed] 48

Bill,

I didn't have time today to find an Excel 2007 machine.  What do you get if 
you add the line

Msgbox c.FormatConditions.Item(k).Formula1
right before the line
bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)

?

Of course, you will get a message for each cell, so only select enough cells 
to see what is going on.

Bernie


"Bill Roberts" <BillRoberts@discussions.microsoft.com> wrote in message 
news:CD02C776-0DC1-45D3-B51E-D92940A163FD@microsoft.com...
> Following is highly abridged code from Bernie Deitrick regarding copying 
> Type
> 2 conditional formats.  Column C is formatted yellow if A1>B1, etc. 
> There
> are 10 rows.  I want to copy the cell color (but not the format equations) 
> to
> column D.  But the line
> bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
> always returns Bcheck=True, then myRet is set to 1, and every cell in 
> column
> D is colored (even where the condition is not true).  I think I need 
> another
> IF statement to check if the conditional format is true (and the cell is
> colored), but I can’t get anything to work.  TIA
> Option Explicit
> Dim R1 As Range
> Dim R2 As Range
> Dim i As Integer
> Dim j As Integer
> Dim k As Integer
> Dim Sel As Range
> Dim myRet As Variant
> Dim bCheck As Boolean
> Sub CopyCFFormatsA()
> Set Sel = Selection
> Set R1 = Range("c1:c10")
> Set R2 = Range("d1:d10")
> j = 1
> Application.EnableEvents = False
> For i = 1 To R1.Rows.Count
> R1.Cells(i, j).Select
> myRet = CheckFormat(R1.Cells(i, j))
> If myRet = False Then GoTo NoCF
> If myRet = "None" Then GoTo NoCF
> R2.Cells(i, j).Interior.colorindex = _
> R1.Cells(i, j).FormatConditions(myRet).Interior.colorindex
> NoCF:
> Next i
> Sel.Select
> Application.EnableEvents = True
> End Sub
> Function CheckFormat(c As Range) As Variant
> CheckFormat = "None"
> For k = 1 To c.FormatConditions.Count
> bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
>    If bCheck Then
>        CheckFormat = k
>        bCheck = False
>        Exit Function
>    End If
> Next k
> CheckFormat = "None"
> End Function
>
> -- 
> Bill Roberts 

0
Bernie
12/9/2009 2:53:12 AM
Great suggestion, Bernie.  The message box always returns "=A1>B1", 
regardless of the cell, so although the conditional formatting does not color 
some cells (condition not true), the macro only and always looks at the 
condition of the first cell.  I am only working with 10 cells so stepping is 
not a big problem.
I didn't think about the message box as a diagnostic tool.  I will work on 
this some more but would very much appreciate your thoughts. 
-- 
Bill Roberts


"Bernie Deitrick" wrote:

> Bill,
> 
> I didn't have time today to find an Excel 2007 machine.  What do you get if 
> you add the line
> 
> Msgbox c.FormatConditions.Item(k).Formula1
> right before the line
> bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
> 
> ?
> 
> Of course, you will get a message for each cell, so only select enough cells 
> to see what is going on.
> 
> Bernie
> 
> 
> "Bill Roberts" <BillRoberts@discussions.microsoft.com> wrote in message 
> news:CD02C776-0DC1-45D3-B51E-D92940A163FD@microsoft.com...
> > Following is highly abridged code from Bernie Deitrick regarding copying 
> > Type
> > 2 conditional formats.  Column C is formatted yellow if A1>B1, etc. 
> > There
> > are 10 rows.  I want to copy the cell color (but not the format equations) 
> > to
> > column D.  But the line
> > bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
> > always returns Bcheck=True, then myRet is set to 1, and every cell in 
> > column
> > D is colored (even where the condition is not true).  I think I need 
> > another
> > IF statement to check if the conditional format is true (and the cell is
> > colored), but I can’t get anything to work.  TIA
> > Option Explicit
> > Dim R1 As Range
> > Dim R2 As Range
> > Dim i As Integer
> > Dim j As Integer
> > Dim k As Integer
> > Dim Sel As Range
> > Dim myRet As Variant
> > Dim bCheck As Boolean
> > Sub CopyCFFormatsA()
> > Set Sel = Selection
> > Set R1 = Range("c1:c10")
> > Set R2 = Range("d1:d10")
> > j = 1
> > Application.EnableEvents = False
> > For i = 1 To R1.Rows.Count
> > R1.Cells(i, j).Select
> > myRet = CheckFormat(R1.Cells(i, j))
> > If myRet = False Then GoTo NoCF
> > If myRet = "None" Then GoTo NoCF
> > R2.Cells(i, j).Interior.colorindex = _
> > R1.Cells(i, j).FormatConditions(myRet).Interior.colorindex
> > NoCF:
> > Next i
> > Sel.Select
> > Application.EnableEvents = True
> > End Sub
> > Function CheckFormat(c As Range) As Variant
> > CheckFormat = "None"
> > For k = 1 To c.FormatConditions.Count
> > bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
> >    If bCheck Then
> >        CheckFormat = k
> >        bCheck = False
> >        Exit Function
> >    End If
> > Next k
> > CheckFormat = "None"
> > End Function
> >
> > -- 
> > Bill Roberts 
> 
> .
> 
0
Utf
12/11/2009 12:35:48 AM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

how to match the color of a button with its background?
Hi, I have a owner draw push button. I associate it with two bitmaps, one is down and the other is up. Is there a way to make the bitmaps's background color match the color of the window where the button is located. I manually choose the best matched color but still the result is not good. I can even see a white border line (not drawn by me) when the button is pushed. Please help. Thanks. Tony Check out http://msdn.microsoft.com/library/en-us/vcresed/html/_asug_choosing_an_opaque_or_transparent_background.asp May Young <tony@srac.com> wrote in message news:<408473...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Copying dbx files from a CDR back into Outlook
My computer was recently hijakced and I had to reinstall everything. I did however save all of my folders in Outlook onto a CD Rom and I do not know how to transfer them back so I can use them. Also I had an animated version of outlook where you could choose loads of diffent stationery and I cannot seem to find that upgrade anaywhere. Can you help me? Tee <tsahai33@hotmail.com> wrote: > My computer was recently hijakced and I had to reinstall > everything. I did however save all of my folders in > Outlook onto a CD Rom and I do not know how to transfer > them back so...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

copy chart from Excel to Powerpoint
I am trying to copy a chart from Excel to Powerpoint, but the button is grayed out and it won't let me copy it. You can convert the chart to a GIF (a picture) with my Excel Objects converter. http://www.geocities.com/excelmarksway "Hoffperson" wrote: > I am trying to copy a chart from Excel to Powerpoint, but the button is > grayed out and it won't let me copy it. ...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

Copy and close code
Hi, I have a worksheet which uses an indirect formula to search through a series of files/sheets based on an address to return a value from cell AE20. Once the data has been copied for all the previous days I would like a copy/paste values to occur to replace any of the values returned by the formula. In AU10:AU381 there is a 1 or 0 for each row. 1 meaning that a copy/pastevalues can be performed (decided by if the date is < today), and zero if i need to keep the formulas. Is it possible to have a copy/paste values code for only sheets 2-4 which works when the file is...

Formatting in CSV
Hi I create a Comma Separated Value file (csv) from my VB application, then open that file with Excel. Excel recognises the commas, and splits the records into separate fields as expected. Can I set properties for rows or columns or cells ? Like bolding, or font size, etc. Thanks Robert -- RobertLees ------------------------------------------------------------------------ RobertLees's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26580 View this thread: http://www.excelforum.com/showthread.php?threadid=486488 Yes. Even though the .csv file contains no fo...

Format List Box!
Can I format a list box which has a query,so if one of the fields called [Spelling] had the word "Spelling" it will change the colour of the font for that row Thanks for any help....Bob It's not possible with the native Access ListBox control. Instead, setup a Subform control to resemble a ListBox and use ConditionalFormatting to achieve the desired look. I think Arvin has a sample on his site here: http://www.datastrat.com/Download2.html -- HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Please respond only to the newsgroups so everyone can ben...