Posts by yjoshi

Following code will most probably solve all your issues.

Private Sub compare()
Dim data, comp As Range


Set comp = Sheets("Sum").Range("A1:A20") 'You can give different or dynamic range here


For Each cell In comp
    For Each sht In Sheets
        If sht.Name <> "Sum" Then ' excludes "Sum" sheet
            Set data = sht.Range("A1:A10") 'You can give different or dynamic range here
            For Each cel In data
                If cell.Value = cel.Value And cell.Value <> "" Then 'compares for value and null
                cell.Interior.Color = 65535 ' this is code for yellow color
                End If
            Next
        End If
    Next
Next


End Sub

I have tested it myself.
Let me know if it does not work :yes:

Andy,

Once we write "=R72<>S72" it automatically results "TRUE" and "FALSE" based on the values of the two cells, so no need for if condition.

Thought just might help

Let me know if i get it right.

You want to Conditionally format a cell if another 2 cells are not equal.

If that is the case, you can write in Conditional Formatting for T72
"=R72<>S72"
and give desired formatting.

This will mean, if R72 is not equal to S72, the conditional formatting will work.

Private Sub Workbook_Open()
Sheets(1).CommandButton1.Visible = True
End Sub

I have attached a sheet in which i tried to create the scenario of copying the formula above the cell instead of having fixed formula.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect
Range("A65536").End(xlUp).EntireRow.Insert
Range("A65536").End(xlUp).Offset(-2, 2).Copy
Range("A65536").End(xlUp).Offset(-1, 2).Select
ActiveSheet.Paste
ActiveSheet.Protect
End Sub

HTH

You can just include one line in the code for that button to make it hidden.

Private Sub CommandButton1_Click()
CommandButton1.Visible = False
End Sub

HTH

Figured out what is the problem...

If the application is in "Design Mode"
Generally First button in "Command Bar"
then the copy paste of rows does the trick.

But could not find a VBA code to enable or disable this "Design Mode".

Will investigate further.. but should give a good headstart for someone else who wants to jump into this.

Sorry... but Highly confusing.

Will File -&gt; Print Area -&gt; Set Print Area work for you?

If not, please specify your problem clearely, and what do you mean by printing in two columns??

Upload a small example please....
it will certainly help to clarify....
I think this is feasible... but still not very clear about the requirement

Regards

I have just returned from vacation,
Give me some time (3-4 Days).
In the mean while you can mail me if you want anything urgent.

Regards

I know you must have thought of this, but by any chance are your pivot tables refer to a data from a file that is not open???

Hi MKNOV,

Thanx for your sincere appereciation of a solution which solved the problem of the original query of perryco, by saying ...

Quote

It makes vague sense to me, but due to being generally useless I need more help!!

As the original post is solved, i do not want to complicate this post.

Please create another post with details of your problem, and we will make sure that it will not be a "generally useless" solution.

Regards,

Humm...

Format -&gt; Row -&gt; Auto Fit.

This will do the trick for you...

(What is happening is that, your cell is taking the line-by-line input, but the width is not sufficient. Increase it or autofit it and it will work

Here comes the code.....

Private Sub CommandButton1_Click()
Dim cell, rng As Range
Application.ScreenUpdating = False
Range("A65536").End(xlUp).Select
Do While ActiveCell.Row > 1
    If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value _
       And ActiveCell.Value <> "" And _
       ActiveCell.Offset(-1, 0).Value <> "" Then
    For x = 1 To 3: ActiveCell.EntireRow.Insert: Next
    End If
    ActiveCell.Offset(-1, 0).Select
Loop


Application.ScreenUpdating = True
End Sub

Also attached a file for demo

Hope this helps

Hi chickenflicker,

Welcome to the borad.

Just for clarification, Window -&gt; Hide is used to hide the entire worksheet.

If you want to hide a particular column(s)
then Click on the Column header(s), then
Format -&gt; Column -&gt; Hide
(OR Just select the columns, press right mouse key and select hide)

Try this

1. Add One Column (Co1 E) called concatinate after the meal.
2. Paste following formula in E2 and copy till your record end
=A2&TRIM(D2)&TRIM(C2)
3. This will give you a unique record for each date, meal and batch (As each employee can have each meal only once)
4. Now in Col F (Count) add following formula
=COUNTIF(E:E,E2)
5. This will give you the count of such unique record, which should be only = 1.
6. Now enable either An Auto Filter or an Advanced filter to weed out the employees where the count is greater that 2.

No Need for Pivot table here man, just keep it simple.

If you want this to be completely automated with VBA, post back.

Attached sheet for your reference.

Hi Dave,

Still, you can't beat me!!

7 April 63 Vs 17 Apr 79 :nana::nana:

I am touched by your modesty!!!!!

I was thinking of writing this, but you did it, and there lies the difference.

You said it so well, that it cannot be said better.

Please accept my best regards, and just to let you know, you are surely special, and you make it feel by your replied.

Great Going Guy.... (GGG)

Hi Jethro,

You look to be determined to get your entire application developed here, but don't mind i like working like this

Its really good that you are coming up with interesting questions one after the another.

I have taken the sheet from your previous post, http://www.ozgrid.com/forum/viewthread.php?tid=1326
so that you will not loose that work. Also, I have proactively added two additional columns in the index apart from Sr. and Sheet Name
1. Description : Currently linked with cell A1 on each sheet
2. Count of records : Currently linked with Cell H1 on respective (which is formatted as ;;; so not visible) which gives the count

I have also amended the Overall Sheet Copy formula to take care of this Index page, other wise, it will messup that!!!

I have also taken the liberty to copy the image you use for Avatar on this site and pasted it on index, and linked the macro to that image, just for fun

Here is the code for Index,

===========================================
Sub create_index()
'By Yogendra Joshi
Dim sht As Worksheet
Dim lnk, lnk_show As String
Dim cnt As Integer
'Clear Existing Data

Application.ScreenUpdating = False
Sheets("Index").Range("A9").Select
If (ActiveCell.SpecialCells(xlLastCell).Row) &gt; 8 Then
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Clear
End If

'Checking for Worksheets and copying data
For Each sht In Worksheets
If Left(sht.Name, 1) <> "_" And sht.Name <> "Index" Then
With sht
lnk = "'" & .Name & "'!A1"
Debug.Print lnk
lnk_show = .Name
Sheets("Index").Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = (Selection.Row) - 8
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = .Name
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, _
Address:="", SubAddress:=lnk
ActiveCell.Offset(0, 1).Value = .Range("A1").Value ' add your own cell if needed
ActiveCell.Offset(0, 2).Value = .Range("H1").Value ' added a hidden cell for count
End With
End If
Next

Application.ScreenUpdating = True
End Sub

===========================================

Hope this helps...

Please do keep coming with more such brellient ideas, someone here will surely put it to reality. :cheers:

Working on it, ....... give 15 Mins....