Re: VBA: Filling areas with different colours depending on column values.
bump :@
Posts by tuppari
-
-
Dear ozgrid,
I have spreadsheet with different data areas separated by a row, where I'd need to fill the whole area with different colours, depending on the values of column D (per area). If the values are all exact same, the area should be coloured green, if not, it should be coloured red.
Please also note, on column E I have marked X's next to lines. This X means the line shouldn' be used in the comparison, but should still be coloured aswell.
If there is only one row in an area (meaning nothing to compare to, excluding rows with X), the area should be coloured green by default.
Attached an example spreadsheet. On the Desired outcome -tab I have deliberately bolded the rows to indicate which values were used for the comparison (the macro does not need to do this)
Example file:
forum.ozgrid.com/index.php?attachment/49300/ -
Re: Range.Select results in error if running macro from another workbook
edit: solved, I just had to insert the macro on a workbook level, not sheet level. Wierd.
-
Here's my problem, I have a very basic line in my macro:
Range("K2").SelectBut, the macro gives me an "application-defined or object-defined error" if I run the macro from a different workbook, than the active one?
I tested this, my macro swaps between two workbooks, and different Range.Select lines work if I transfer the whole macro to another workbook.
-
Re: VBA Macro to find duplicates from multiple columns
Quote from jindon;628522See
Dear Jindon, your solution now works perfectly, with one but! We have values with * as the last character, and thus excel reads them as wildcards. Is there a workaround for this?
-
Re: VBA Macro to find duplicates from multiple columns
Dear Jindon & Gopala,
Thank you for your input, but I was unsuccesful with either of these formulas : (.
-
Dear Ozgrid,
I have a workbook with multiple columns with differiating data. I would need to compare the values of Column A to every other column with data on it, and highlight duplicates. (Note, only column A should be used as a comparison value. So A should be compared with B C and D, but B should not be compared with C or D.
Attached example workbook.
forum.ozgrid.com/index.php?attachment/48489/
So this is what my start setup looks like.
[TABLE="width: 320"]
[tr]
[/tr]
[TD="class: xl63, width: 64"]User1[/TD]
[TD="class: xl63, width: 64"]User2[/TD]
[TD="class: xl63, width: 64"]User3[/TD]
[TD="class: xl63, width: 64"]User4[/TD]
[TD="class: xl63, width: 64"]User5[/TD]
[tr]
[td]X1
[/td]
[td]X1
[/td]
[td]X5
[/td]
[td]X4
[/td]
[td]R2
[/td]
[/tr]
[tr]
[td]Y2
[/td]
[td]Y2
[/td]
[td]R2
[/td]
[td]X3
[/td]
[td]Y2
[/td]
[/tr]
[tr]
[td]Y3
[/td]
[td]Y3
[/td]
[td]Y4
[/td]
[td]Y1
[/td]
[td]Y9
[/td]
[/tr]
[tr]
[td]X2
[/td]
[td]Y4
[/td]
[td]Y7
[/td]
[td]Y2
[/td]
[td]X1
[/td]
[/tr]
[tr]
[td]X3
[/td]
[td]Y5
[/td]
[td]Y3
[/td]
[td]Y7
[/td]
[td][/td]
[/tr]
[tr]
[td]Y4
[/td]
[td]X2
[/td]
[td]X1
[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Y5
[/td]
[td][/td]
[td]X2
[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]X4
[/td]
[td][/td]
[td]X4
[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]X5
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]R2
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/TABLE]And this is what it should look like after running the macro
[TABLE="width: 320"]
[tr]
[/tr]
[TD="class: xl63, width: 64"]User1[/TD]
[TD="class: xl63, width: 64"]User2[/TD]
[TD="class: xl63, width: 64"]User3[/TD]
[TD="class: xl63, width: 64"]User4[/TD]
[TD="class: xl63, width: 64"]User5[/TD]
[tr]
[td]X1
[/td]
[td]X1
[/td]
[td]X5
[/td]
[td]X4
[/td]
[td]R2
[/td]
[/tr]
[tr]
[td]Y2
[/td]
[td]Y2
[/td]
[td]R2
[/td]
[td]X3
[/td]
[td]Y2
[/td]
[/tr]
[tr]
[td]Y3
[/td]
[td]Y3
[/td]
[td]Y4
[/td]
[td]Y1
[/td]
[td]Y9
[/td]
[/tr]
[tr]
[td]X2
[/td]
[td]Y4
[/td]
[td]Y7
[/td]
[td]Y2
[/td]
[td]X1
[/td]
[/tr]
[tr]
[td]X3
[/td]
[td]Y5
[/td]
[td]Y3
[/td]
[td]Y7
[/td]
[td][/td]
[/tr]
[tr]
[td]Y4
[/td]
[td]X2
[/td]
[td]X1
[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Y5
[/td]
[td][/td]
[td]X2
[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]X4
[/td]
[td][/td]
[td]X4
[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]X5
[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]R2
[/td]
[/tr]
[/TABLE]The purpose of this macro is to find values from columns B - XX, that aren't found in column A.
As from this example, the values Y7 and Y9 were found.In the data workbook we're using, there's around 70 columns of data.
-
Re: VBA Macro to separate data
Quote from royUK;627438Attach an example file with some dummy data
Attached an example.
Basically I need the Technical names in one row in their own cells, and each attribute listed on multiple rows under the correct column (which is specified by the "TECHNICAL NAME"
forum.ozgrid.com/index.php?attachment/48395/
edit: So basically I need to have the technical name only once, but each unique attribute must be listed.
-
I have a worksheet with data in the following format;
[TABLE="width: 500"]
[tr]
[td]A
[/td]
[td]Value1
[/td]
[/tr]
[tr]
[td]A
[/td]
[td]Value2
[/td]
[/tr]
[tr]
[td]A
[/td]
[td]Value3
[/td]
[/tr]
[tr]
[td]B
[/td]
[td]Value4
[/td]
[/tr]
[tr]
[td]B
[/td]
[td]Value5
[/td]
[/tr]
[tr]
[td]B
[/td]
[td]Value6
[/td]
[/tr]
[tr]
[td]C
[/td]
[td]Value7
[/td]
[/tr]
[tr]
[td]C
[/td]
[td]Value8
[/td]
[/tr]
[tr]
[td]C
[/td]
[td]Value9
[/td]
[/tr]
[tr]
[td]D
[/td]
[td]Value10
[/td]
[/tr]
[/TABLE]What I'd need is a VBA macro to sort the data into multiple columns with only one topic, with all the values below which were next to the same topic, in a format following;
[TABLE="width: 500"]
[tr]
[td]A
[/td]
[td]B
[/td]
[td]C
[/td]
[td]D
[/td]
[/tr]
[tr]
[td]Value1
[/td]
[td]Value4
[/td]
[td]Value7
[/td]
[td]Value10
[/td]
[/tr]
[tr]
[td]Value2
[/td]
[td]Value5
[/td]
[td]Value8
[/td]
[td][/td]
[/tr]
[tr]
[td]Value3
[/td]
[td]Value6
[/td]
[td]Value9
[/td]
[td][/td]
[/tr]
[/TABLE]I am quite sure there is a macro already done for this purpose, but I couldn't find one for this need.