include conditional formatting with TRANSPOSE function?

Hi all,

I have transposed some data in excel from one sheet to another but I would like to take the conditional formatting with the data if possible.

Please see attached workbook for reference.

Background:
The data is to illustrate which individuals in an organisation have access to which files on our system. In workbook Sheet 1:
row 1 is row of individuals column A denotes groups or folders. If an individual is a member of the group then "Y" is entered in the cell. The top portion of the spreadsheet is conditionally formatted red if cell is blank and green if the cell contains "Y" to illustrate which individuals are in which group.

Below are the folders. Access to a folder is dependent on being a member of a particular group or groups so OR functions are used to determine if cell for folder access is true or false. The conditional formatting for most rows in this section (all but row 21) is set to green for TRUE and red for FALSE.

However some groups give READ ONLY access to a folder. This is illustrated in row 21. For folder 7 read only access is granted if an individual is a member of group h, full access if a member of group b or group f. Since I can only generate TRUE or FALSE using the OR function for the cell the conditional formatting is based on the cells above.

When i transpose the data (Sheet 2) the formatting is lost. I would like the same formatting on the transposed data (this would still be dependent on the person/group relationship cells). If it was just access or no access without read only this would be easy as I could just recreate conditional formatting of green for TRUE and red for FALSE in the transposed cells but is there a way to maintain the formatting of the transposed cells so that the orange (for read only access) is maintained?


Can this be done? Any help much appreciated!

LizzyJAttachment 338275

Hi,

Thanks for replying to my query. I tried to create a macro with the code you supplied but then when I try and run the code on the destination cells it gives me an error message:

"Run-time error '1004':
You cannot change part of an array"

I have not worked with macros before and unsure what to do. I was trying to run on the sheet 2 cells as per my initial attached workbook. Also can you confirm the code supplied will transpose the colours and not just copy them in the arrangement they are in?

Any help much appreciated again.

LizzyJ

Hi,

So I tried running the macro again on some cells that were not an array and it worked perfectly. The problem is specifically want to run the colour macro on cells that are an array- sheet 2 of my original workbook (transposed array from sheet 1).

The macro also changed the content of the blank cells to =Sheet1!I16 for example. I do not want to lose the cell content. Sheet 2 is an array based on sheet 1 so that if i update sheet 1 it will automatically update sheet 2. The reason I want both is so that i can filter by folder or by person (i.e. i can go to Sheet 1 and filter the person column for true and see which folders that person has access to, or I can go to Sheet 2 and filter the folder column for true and see which people have access to that folder).

I do not want to lose this functionality I simply want to apply the same colours to the array on Sheet 2.

Any ideas?

LizzyJ

Thank you so much that perfectly maps the colour onto the array.

Is there a way though to link the colour to Sheet 1. When Sheet one data is updated e.g. a cell becomes FALSE the colour automatically changes to Red. The data on Sheet 2 automatically changes to FALSE in the array to match Sheet 1 but the colour remains Green. Is there a way to link the colour so that it automatically updates in Sheet 2 when it does in Sheet 1?

If not I know I can run the macro again to update the colours accordingly but it would be perfect if it could do it automatically.

Thanks

LizzyJ

AMAZING!

Thank you so much for all your help!

I need to learn how to do that!

LizzyJ

Hi,

I have just applied the code to the actually workbook (far bigger and more complicated than the sample i sent you) and it works perfectly but I have one more question.

In the big spreadsheet i have resized columns so that more fit in view at once so you cannot read the work true or false it just comes up with #. In the 'Sheet1' where the conditional formatting is applied I have also applied a conditional formatting to the font colour so that visually the information is simnply displayed as coloured squares (the text is the same colour as the fill).

Is it possible to add this to the code so that the font colour changes as well?

Thanks

LizzyJ

The reason I want to keep the formula is so that I can still filter by true or false in the columns.

i.e. I can go to a folder and filter by TRUE to see who has access to that folder.

I do not know of a way to just filter by the colour of the cell- I dont think that is possible(?) so keep the cell content for filtering btu it looks neater is there are no '#' symbols.

Thanks again for the help