Posts by Joozh

Re: Auto Resizing Of Comments

Cross posting on the same forum is a no no but do you have a problem with posting the same requesting to some other forum and especially if one is really stuck and desperate for help. I'm really interested in knowing why you have a problem with that what AND MORE IMPORTANTLY exactly did you achieve by posting the message above?

Have a nice day!

I have an excel file (about 100 records so far but likely to grow) in which 1 column is used for storing very important comments. The problem is that at the time of entering the comments I have to manually re-size the comments box and secondly when I try to read a comment, I have to manually drag (and sometimes resize) the comment box to read the whole comment.

Can someone help me with a solution which will automatically re-size the comments box when I want to read it at the time of moving the cursor over the field.

Thanks.

Hi,

I have a MS word 2003 document which has tables. Have been working with this document for a long time and never had the following problem until now:

When I open this document the table boundaries are visible in the Print Layout view but not in the print preview mode. I have tried CTRL+SHIFT+& and have also tried various options from Tools | Options | View | Print and Web Layout options but the silly borders around the table are still showing. Interestingly I have even tried selecting the table and set the borders etc. to nothing and even white colored lines but still no luck

Can someone help me out please.

Thanks.
P.S
I hope I am not posting this in the wrong place

Re: Mark Cell Common Between 2 Lists

Hi Mike: Thanks a lot. Works great

Hi Dave: Thanks for the reply. Found them

Hi,

I have 2 lists in column A and column B. For each cell in column B, I want to put a "YES" in the corresponding cell in column C if the contents (of the cell in column B) are somewhere in the entire list under column A (I think the list in both columns is about 5000+).

Will appreciate your help.
Thanks.

P.S
I have logged in after quite a gap and found that all my subscribed threads (gathered over a year) have vanished :oops:

Admins: Is there any way you can help me recover these please

Re: Remove Cell Borders With Macro

Quote from Andy Pope

To remove the borders use this instead of the BorderAround method, which does not seem to work when removing borders.

[vba] .Borders.LineStyle = xlLineStyleNone [/vba]

Hi Andy,
Thanks for the quick feedback. Can you please have a look at the code in the attached file. It's almost similar to the earlier file. There are 2 points that have totally confused me. You'll see these 2 in the form of comments in the code.

Can you kindly help and guide.

Thanks.

Re: Remove Cell Borders With Macro

Quote from Andy Pope

post an example workbook rather than an image and we can test it.

Hi Andy,

The file is attached. Will appreciate your help.

Thanks.

Hi,

I am simply doing a comparison of text in column A against column B and am using the following simple formula in column C

[INDENT][INDENT]= IF(A1=B1,"Same","Different")[/INDENT][/INDENT]

The situation which I am trying to solve is when the text in both columns is the same but in different cases e.g. If A1 = Apple and B1 = apple, my formula is giving the result "Same" in C1 whereas I expect the formula to distinguish and thus return "Different" as the result.... I hope you get the picture.

How do I improve my formula so that it can differentiate between different cases (Upper, Lower, etc.)

Thanks.

Re: Remove Cell Borders With Macro

Thanks Dave and Andy for your recommendation regarding using the macro recorder as a learning point....

Andy: No, I have not used any conditional formatting. I have manually and randomly set the color of the cells (in the image that I attached). I'm surprised that the code is working for you :confused:

Re: Formatting Of Cell Borders

Thanks for the reply Dave. However there's got to be solution without having to copy/paste the resulting code from the macro recorder. I have read that the code automatically generated by the macro recorder is not the most efficient one.

Since I have just started learning VBA, therefore I am trying by best to follow "best-practices" from the very start... Given your expertise, I hope that you'll give me a pat on the back for being on the right track :roll eyes: :unsure:

Hi,

I'm still playing around and trying to explore the VBA ocean :cry:

The code below is self explanatory but it does not undo the border related formatting. Can you please guide and help me figure out my mistake. The attached image shows the worksheet that I am using.

Sub Exercise5()
' This code is supposed to do the following:
' If the cell is highlighted with yellow fill, format it's borders, display a dialog Box/MsgBox 
' with some information about the cell and when the user clicks OK on the MsgBox, 
' it should [B][SIZE="4"]undo[/SIZE][/B] all border related formatting


Dim Rng As Range, MyCell As Range
Dim irow As Long


Application.ScreenUpdating = False


Set Rng = Worksheets(1).Cells(1, 1)
irow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Rng.Resize(irow - Rng.Row + 1, 1)


On Error Resume Next


For Each MyCell In Rng.Cells
  If MyCell.Interior.ColorIndex = 6 Then


[B][COLOR="Red"][SIZE="4"]' Tried both of the following lines MyCell.Activitate and MyCell.Select 
' but could not get the cell to be selected.
' Can you please help me understand why I am unable to 
' get the cell highlighted?[/SIZE][/COLOR][/B]
 '     MyCell.Select
 '     MyCell.Activate
      
      With MyCell
        .BorderAround LineStyle:=xlDash, Weight:=xlThick, ColorIndex:=5
      End With
      
      MsgBox "Cell Address = " & MyCell.Address & vbLf & _
          "Interior.ColorIndex = " & MyCell.Interior.ColorIndex & vbLf & _
             "Cell.Value = " & MyCell.Value
      
      '[B][COLOR="Red"]The code below [B][SIZE="4"]does not undo[/SIZE][/B] the border related formatting that was done above[/COLOR][/B]
      With MyCell
        .BorderAround LineStyle:=xlContinuous, Weight:=xlThin, [INDENT][INDENT][INDENT]_ColorIndex:=xlColorIndexAutomatic[/INDENT][/INDENT][/INDENT]
      End With
             
  End If
Next MyCell


Application.ScreenUpdating = True


End Sub

Thanks.

Re: Delete Highighted Rows

Quote from Reafidy

This will delete the entire row of any cell in column A if it is highlighted or blank.

Code

Sub HTH()
    With Worksheets("Sheet1").Columns(1)
        Application.FindFormat.Interior.ColorIndex = 6
        .Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
                 xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=False
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Hi Reafidy,

This is some awesome stuff. To be honest and since I'm a beginner on the VBA side, the simple approach suggested by ska was easy to follow.

However when I looked more and more into your suggestion, I got simply impressed and confused Impressed becuase as Dave said "Now, that's good efficient code. Love it!".

And confused becuase I am stil trying to breakup the solution in small portions so that I can figure out how the whole thing works. I have not had much success yet but hopefully I'll finally be able to figure all out (one day)... Will appreciate of any one can give some pointers explaining how you code works.

Thanks.

Re: Delete Highighted Rows

Hi everyone,

Thank you for your feedback, replies, and help. Kindly allow me to get back to you after going through the replies in detail.

Thanks again

Re: Delete Highighted Rows

Thank for the reply. That was mistke on my part (while doing a copy /paste of the code). The Nrxt r1 statement is very much there but the dubug.print statenebts are sill printing from row
$A$49305 -4142
$A$49306 -4142
$A$49307 -4142
$A$49308 -4142
Where did the Debug.Print output go for the cells startuing from $A$1 and so onwards :confused.

Also can you please help me wite the missing part of the code. I sense I am almost there but ..

HI,

I have a worksheet with a single column (Column A) containing about 50,000 entries. I have tried folowing code (which is not complete yet)... I was about to move forward when I realized that the Dubug.Print statements are giving strange results. Below are only the 1st three lines which the Debug,Print statement has churnd out:

$A$49305 -4142
$A$49306 -4142
$A$49307 -4142

What happended to $A$1, $A$2 and so on?? Also can someone please help me complete the code. The attached image should give you an idea of how my worksheet is setup and below is the code I have so far:

Sub DelNonHighlight()
'
'Delete all rows in which the 1st cell (in Row A) is NOT highlighted




Dim Rng As Range
Dim r1 As Range
Dim irow


Application.ScreenUpdating = False


Set Rng = Worksheets(1).Cells(1, 1)
irow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Rng.Resize(irow - Rng.Row + 1, 1)
MsgBox Rng.Address




On Error Resume Next


For Each r1 In Rng
' Here I want to check if the current cell (pointed to by r1) has a yellow highlight. And
' if so, I want to move to the next row, If the Current cell is NOT highlighted, I want to ' delete the row and move on to the next cell


[B][COLOR="Red"]    Debug.Print r1.Address & "   " & r1.Interior.ColorIndex[/COLOR][/B]Next


Application.ScreenUpdating = True


End Sub

Hi,

I am working on an exercise in which I am accessing/referring to the 1st, and 2nd sheets using the following lines of code

' code not relevant to the question
'....
Set Rng1 = Worksheets("Sheet1").Cells(1, 1)
Set Rng2 = Worksheets("Sheet2").Cells(1, 1)
' rest of code not relevant to the question
'....
'....

Instead of using the sheet names I would like to access/refer to the sheets with their sheet #'s instead of their names (as I am currently doing).

Can you kindly guide me.

Thanks.

Re: Autofilling Of Code Feature

Hi Andy,
Hope you're doing great

Thanks for the reply, the suggested code, and for telling me that "Aufill" feature is called Intellisense.

Please let me try your code and get back in case i run into problems.

Hi,

Don't know what the feature is called but I'm referring to it as the "AutoFill" feature of the VBA interface where the possible code is automatically listed. For Example, if I type Range. , a list of possible methods (the yellow one's) and properties pops-up up.

However for a code like following, this does not happen. I mean I do not get a list of the [COLOR="Red"]2 lines highlighted[/COLOR] in the portion of code below... Can someone please help me clear my concepts:

...
For i = 1 To RowCount
  If Range("D" & i).Value = "Y" Then
    MyRng.Range("A" & i & ":H" & i).Select
      With Selection.Interior
[COLOR="red"][B]
         .ColorIndex = 6
         .Pattern = xlSolid
[/B][/COLOR]
    End With
  End If
Next i

Re: Highlight Cells Meeting Condition

Hi Brian,

I am attaching my sheet. CAn you kindly have a look. Thanks.

Re: Counting The Number Of Columns

Quote from Brian Walters

I think Rows.Count returns 65536, Excel's maximum number of rows. Therefore starting at row 65536 or 65535 (65536-1) will give you the same answer when you do the xlUp.

[COLOR="Purple"]I think you are right. Makes sense... Thanks[/COLOR]

Quote from Brian Walters

I'm not sure why you need to count the columns since you said you would highlight the columns A thru F of a row that meets your criteria

[COLOR="Purple"]You are right. Since I'm in the process of learning VBA, therefore I just wanted to learn how to dynamically count the total # of columns... Never know, your tip might come in very handy sometime in the future :)[/COLOR]

Quote from Brian Walters

.. try using this to get the last row and last column

Code

EndRow = Cells.SpecialCells(xlCellTypeLastCell).Row
EndCol = Cells.SpecialCells(xlCellTypeLastCell).Column

[COLOR="red"]Brian, the 2nd line of code (EndCol = ...) gives the correct result but the first one (EndRows = ...) gives incorrect result. In my example sheet, i have 5 columns and 8 rows. I get the correct # of columns but get 18 as the answer for # of rows :confused: Any clues as to what could be the reason?[/COLOR]