Can we Get/Apply Information of the Conditional Formatting used in any cell using VBA?

Can we Get/Apply the Information of the Conditional Formatting used in any cell using VBA?

Dear Forum,

In one of my recent posts, I received help in getting the information of the Formula, Value and various aspects of Formatting..
However, is it possible to get the information for the Conditional Formatting which is applied in the cell whose information I seek..

Like for Example :

I need to find the information for a particular cell which has a conditional formatting such as if the value contains the number between 0-25 then the Font Colour is RED , if the value is 26-50 then Orange and if the Value is 51-75 then Green and if the last range 76-100 is Blue.

Now I know how to apply the same using conditional format dialog box manually however I need to extract this information from an already existing conditionally applied cell..as Information as well as I should be able to replicate this same information to a different Sheet using VBA..

Warm REgards
e4excel

Dear Forum,

One more thing I am also looking to get the information for the Borders used in the original Sheets data range to be replicated in the different sheet using this information...

Maybe something like this would work for border info.

Please Login or Register  to view this content.

If a cell has a Font Color red due to a conditional formatting applied on the cells value can we record just the value for the RED Color for the Font..

Yes, that is easy. But I would have assumed that you wanted the conditional formatting applied rather than just the color. And I'm unsure how to do that.

Let me mention that you have a nice style of writing the VBA code which even a formula person like me feels easy to understand.

Thank you. That is, of course, intentional. And I worked hard to develop that style. Although, as a Chess Coach I'm used to taking complex ideas and expressing them so that those with less experience can understand them.

Ok Understood now that the numbers 7 to 12 stand for the various sides of a cell, but just out of curiosity, why it did not start from 1, then what does 1 to 6 or 5 stand for?

How do they say this: "It is outside my pay grade." :-)

What I did was this. I recorded a macro where I applied borders to a region. Then I looked at the recorded code and played around with it. As I played around with it, I made the discoveries which I illustrated in my previous message. But to be honest, that is the limit of my knowledge. I don't know any more than what I showed you.

it gave me the Diagonal Lines though it was not present in the original..

I stated as such in my previous post. So I think my procedure has limitations and isn't foolproof.

Hi StevenM,

Thanks for the response, I tried the code it does make the Borders but somehow strangely the colours are getting interchangedTHe color for the Inside Verticals and Inside Horizontals are getting interchanged..

Can you please help me with , getting the colour for the conditional formatting used..
Is it different than what you ave helped in the earlier query

Yes, that is easy. But I would have assumed that you wanted the conditional formatting applied rather than just the color. And I'm unsure how to do that.

If the latter is not possible then can I just get the color information for a cell which has derived the color FillCOLOR and FONT color FONT size any such changes which can be possible due to conditional formatting..

Thanks in advance..

Hi Joseph,

Actually both, but if the latter is extremely difficult then just getting the information would be fine, but then let me just confirm one thing..
In certain cells we have a default Fill Color, Font Color , Font Size and on the conditional formatting condition meeting the appearance changes so will it give me the information of the existing color or the color which is got changed due to the conditional formatting.

Thanks in advance

Okay, I think I've solved the problem of formatting conditions.

Take a look at: e4excel_CreateTable_v2.xlsm

And run: Run_CreateTable

I realize that you have said you don't like this approach, but perhaps you can adapt from my code what you need.

There is one mistake:

Please Login or Register  to view this content.

The first apostrophe in the above line needs to be deleted. It turns the font color white, and before I added the conditional formatting, a white font on a white background looks blank.

The only thing I do not follow is when I tried to get the information for the cells with no BORDERS i.e cells selected with FORMAT - BORDERS - NONE but they still produce a regular border.
I want the code to record some value which means that there is no BORDER

So that was the flaw in my code, but the following corrects that.

Please Login or Register  to view this content.

My assumption is that if you add .weight or .color to a border, it will add a default line style. But if you check if the line style is = xlLineStyleNone before, you can stop the code from adding a border.

Thanks Steven very sweet of you to reply so quickly actually I was searching for the condition to check whether it .xlLineStyleNone when you replied..
One more thing , I am using your code but in the old style from the first help..

Can you please help me on the same, a I have yet to use the above new approach, I mean i m going to use it later on but I started with the old approach..

Please Login or Register  to view this content.

This was to display..
and this is to actually format the new sheets border based on the REPORT Sheets Border style.

This line gives me the FOrmatting but how do i check whether it is NONE or not?

Please Login or Register  to view this content.

How do I write the NOT condition in the above line..

And also, while researching I found out that there are several other aspects about Line Borders which are also important such as the below:

Please Login or Register  to view this content.

Thanks in advance..

An If statement is a true or false statement. A not negates a Boolean statement or variable.

So maybe something like:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Or something like that.

Any Ideas on the below:

'XlLineStyle can be one of these XlLineStyle constants:
'xlContinuous
'xlDash
'xlDashDot
'xlDashDotDot
'xlDot
'xlDouble
'xlSlantDashDot
'xlLineStyleNone


'XlBorderWeight can be one of these XlBorderWeight constants:
'xlHairline
'xlThin
'xlMedium
'xlThick

'The color is specified as an index value into the current color palette, or as one of the following XlColorIndex constants:
'xlColorIndexAutomatic
'xlColorIndexNone

Thanks it worked like a charm, I was not able to use it for such a longtime as I made a mistake in your code.. by putting an "." before xlLineStyleNone
My mistake..

In most of my work I have used Dotted Lines as well as does that mean when I am considering these different aspects then i need to also consider the different sides too for each aspect..

Try this.
It collects all the information for borders for each cell.
Then it recreates those borders.

In the line:

Please Login or Register  to view this content.

The first range already has a border.
The second range will copy the border info of the first range.

Please Login or Register  to view this content.

Conditional Formatting information for the applied color and not the Formula..
I am just reiterating that I would be happy if I could just get the Color or COlorIndex , Fill COlor, Font Size:- No(s) or any other aspect which conditional formatting can influence i.e superficial color no derived from conditional formatting..

So if a cell has a default black font color and the conditional formatting renders it a REd color then can I get the answer as the COLOR or COLORINDEX NO for RED color and not BLACK..

I'm not for sure what you're asking.

As for Color & Colorindex, I suspect that Color will always be more precise and that one shouldn't use colorindex. But I could be wrong.

I don't have a lot of experience working with color formatting. I've worked on two workbooks with it, yours and one other. In that other workbook, I just copied the color formatting, and pasted it into another cell. So really, everything I know has been by playing around with your workbook.

In the file you uploaded, the color formatting was a phrase plus an interior color. The way I understood it, the color of the font was just the property of the cell, but perhaps I could be wrong about that. Hmm ... I just ran a test, and I was not able to pick up the font color from the formatting. So unless I made a mistake, I was right. The font color was separate from the conditional formatting.

No doubt conditional formatting can be more complex than what is in your workbook. But in your workbook, the color formatting was 4 sets of a phrases and interior color for each phrase. It would be easy to retrieve that information and store it as needed.

I did a Google and found: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx
I have no idea if it will help you or not.

Hi Steven.

If thats possible please let me know, so that I can close the thread.
The problem is that when I replicated the Report sheet which has default FOnt and Fill Color as NO COLOR it just pastes the contents with the same default color ignoring the COnditional Formatting which is rendering different FOnt and Fill colors based on the contents in the cell.

Thanks in advance

I don't think I know anything on this issue more than what I've already posted. Frankly, it has been a learning experience for me, and I thank you for that. I've learned a lot playing around with your spreadsheet and exploring these issues. But I've never understood exactly what you were doing, or why you wanted to do it the way you are doing it. If I felt that I could help you again in some small way, you know I would. My non-response is merely due to the fact that I've exhausted all my knowledge on this issue and I don't think I have anything to add. As for the code you posted, how would I know if it worked or not? I can't just read code and know what it will do. As my bio makes clear, I don't claim to have anything more than a rudimentary understanding. While I'm willing to share my rudimentary understanding when I can, time and time again I've seen others post code far superior to mine.

Yeah but those codes are so superior that I find it too difficult to extend or add to it and therefore I liked your approach simple yet can be improvised.
Actually I making some Sheets where I need to get the replicas but when I tried just copy pasting it worked but it takes too much time and I think this approach is better to re-create a sheet using everything as if formulas and the decided formatting and then just convert it into values.

It saves on time and the coding does not get revealed..

Dear Forum,

I found a link for my problem where I need the color codes for cells filled with conditional formatting.
There's a link but I have some issues with my Download mgr and unable to do so..
Can someone please try downloading and pasting it here as I can download from the forum without any hassles.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=190

Thanks

Dear Steven,

I found this code in the earlier link, now how I use this code over your code whenever there's a conditional formatting applied, I mean that I dont want to specify which cells has conditional formatting or not whichever has it they need to use this code and dont then your code and the color code should be derived..

Please Login or Register  to view this content.

its not working at all..

Are you using the same workbook as before? (If so I have a copy of it.) If not, can you upload your workbook?

Yeah the same one from the other query where you helped

So can you post the whole code, and point out which part is giving you problems?

Yeah, actually I was trying to do that but every time I am doing that my nets going bad..
Sorry...

Please Login or Register  to view this content.

Theres no error which is being thrown but inspite of using the Function ConditionalColor its not working and the Column D in the Dump SHeet remains without any color..

SO I want you to please correct me where I am mistake in entering the code for FOnt and Interior in the lines where the coloring happens for the Dump Sheet

Please correct the code wherever neccessary

Where did you get the function ConditionalColor? It wasn't part of the code you posted.

PLease check post 29 , 28 and I have pasted the code in modules in my file

Theres no error which is being thrown but inspite of using the Function ConditionalColor its not working and the Column D in the Dump SHeet remains without any color..

SO I want you to please correct me where I am mistake in entering the code for FOnt and Interior in the lines where the coloring happens for the Dump Sheet

First of all, your conditional formatting doesn't have a font color. I believe I had mentioned this before.

Run the following code on your worksheet.

Please Login or Register  to view this content.

Dear StevenM,

First of all, your conditional formatting doesn't have a font color. I believe I had mentioned this before.

I think I have made changes in the file and as my PC and net is slow and am not able to trace the old file, however I am uploading the File in contention to save trouble..

I have made a few changes in the File..
There are 4 Sheets:
1 Data
2 Report
3 Formatting Information - This is used to store the information of the Range
4 Replica - This is to create the replica of the Report Sheet.

In the Replica, everything gets written i.e the contents the Regular Formats like Font Color which is done Manually in the Report sheet except for the column D where there's conditional formatting used..
The contents do get copied but do not get displayed, strangely they have a White Font Color and the same regarding the Fill Color..there's no change in that though there are 5 different Conditional Formats set in the Report Sheet.

I liked the last solution, I mean its so good that I think one doesn't need to even specify the range, however how do I use that in my case..

Please advise on the same..
Thanks in advance..

I mean its so good that I think one doesn't need to even specify the range, however how do I use that in my case..

You must supply the function GetFormatConditions with at least one cell within the range which has format conditions. Then from that one cell, it can pick up all five conditions.

The function returns a string with three items for each condition: (a) Formula1, interior color and font color. So with format conditions more complex than that, my code will be inadequate.

Also note, Formula1 cannot contain a comma, because I use a comma as a marker in the string. (Of course, if a comma is used in Formula1, my marker can be changed.)

The string is returned with two types of markers: (1) vbCr is used to separate the different format conditions, and (2) a comma is used to separate each element in a condition.

Please Login or Register  to view this content.

Of course, you need to replace "Formula1, Interior Color, and Font Color" with where ever you want to write these values.

Dear Steven,

May I please request you to add the two codes and then upload an attachment..
I am not able to follow what and where I need to make the additions with your code as well as the code which I mentioned in the Post # 29 and the link for the same in the Post # 28..

Are these the lines where I need to make the additions :

Please Login or Register  to view this content.

So humble request upload the Sheet where you have made the changes as well as see the other code if thats more superior in terms of handling more criteria not that your code isn't it..
I honestly liked your code as it mentions the condition criteria and then the FOnt Color and Interior Fill COlor but as far as this requirement goes I would need just the Color Code(s) for Font as well as Fill Color..

Please Login or Register  to view this content.

So please thanks in advance..

Warm Regards
e4excel

What part of my last message did you not understand?

Hi Steve,

Honestly, I thought that the last post #43 contained the code from the actual excerpts from the function but now after re-reading your post several times I realized that I missed that as a part of the Function..

I am not so familiar with VBA and the last 2-3 codes and functions are a little too heavy for me..I mean i have not understood them completely but still get the results well but when it comes to applying it does reveal my folly..

Uptill now I was just asking , if it was possible to get the formula of the conditional formatting but I think you have already made provisions for the same..

I dont have anything as Formula1 till now, I mean I have the cell formula in my code which you had provided which gives the formula in the cell but now that is already happening the things remaining were the Font Color and Fill Color or the Interior Color incase of the Conditional Formatting Applied Cells..

Please Login or Register  to view this content.

Just to confirm that I need to add those lines in the Code in the Sheet Formatting instructions Right?

Please Login or Register  to view this content.

Like this nah..

Warm regards
e4excel

Originally Posted by StevenM

What part of my last message did you not understand?

Dear Steve,

Sincere apologies, but believe me
Though I like all your solutions/codes provided till now it still is difficult for me to really adapt so fast..
So please please forgive me for that..

I still don't know what you are asking.

The function I wrote: GetFormatConditions collects thee items from format conditions: (a) Formula1, (b) interior color, and (c) font color.

I would assume that format conditions can get more complex than that, so if you need more information than that, my GetFormatConditions function won't be of much use. But it seems to me that this might be acceptable for your use.

One of the problems is that a cell can contain more than one format condition. And my function returns them all.

But it returns them all in one long string. I don't know what your needs are, nor what you are trying to accomplish.

In your last workbook, you had five format conditions, and with three items per each, that is 15 pieces of information (all in one lone string).

As I tried to illustrate in message #43, there is a simple way of getting to this 15 pieces of data by using the Split function. The first split divides the string in 5 parts (based on 5 formatting conditions). The second split divides each of the 5 parts into three parts: (a) Formula1, (b) interior color, and (c) font color.

So if you want to write their information into a cell, one cell for each item, then you have to loop.
Maybe something like:

Please Login or Register  to view this content.

You need to modify Cells, nRow, nCol to suit your needs.

Now I actually could understand some part of it, really appreciate your patience with me..
How the code was working..

But I am still lost ,as though your function is extremely useful I am not sure how to go about further?
I was even trying to integrate the values from the other function but in vain..

I think all the major part is over except the tail..

What I am asking is? In the uploaded File , if you click the command button in the Formatting Information Sheet, it goes to the Replica Sheet and starts creating the exact replica however in the column D though the values appear they do not get displayed as the Font is White or No Color..
I only want this to have the same color as the Parent Sheet REport Colund, In the Parent sheet the colors are rendered by applying conditional formatting whereas over here I want it to be applied using VBA,

Using these two lines I was applying color nos

Please Login or Register  to view this content.

However the normal functions used were not recording the color nos acquired due to Conditional Formatting, now with your coding as well as the other code I can get the colors but do not how to pass it to these above lines..

As this is happening in a loop and each cell has a different format is that a challenge?

Are you wanting to collect the data of the formatting conditions as text?
Or are you trying to set the formatting conditions in a range?

Message #48 assumes the latter.
If you are trying to set format conditions, you would need something like:

Please Login or Register  to view this content.

You would need to modify .Range & whateverRow.