Posts by rajeshn_in

Hi Friends,

I had posted the same issue yesterday in below web site, I did'nt get solution, thats way I am posting here. Please consider my apology if it is wrong.

https://www.excelforum.com/exc…f-format.html#post4872602

Issue :

I am using below excel with wrap text. in Excel document its showing correctly, but when I am trying for PDF then some line are not coming properly. Please see attachments for better understanding. Some cells using extra space at the bottom. Some cells not coming in to box at the bottom.

Thank you.

Finally I got a code as below :

[vba]

Sub Remove_Color3()

Dim i As Integer

Application.ScreenUpdating = False
For i = 2 To 255

If Cells(i, 1) = Cells(i + 1, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 1, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 1, 1).Font.Bold Then
Cells(i + 1, 1).Interior.ColorIndex = iNone
Cells(i + 1, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 2, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 2, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 2, 1).Font.Bold Then
Cells(i + 2, 1).Interior.ColorIndex = iNone
Cells(i + 2, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 3, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 3, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 3, 1).Font.Bold Then
Cells(i + 3, 1).Interior.ColorIndex = iNone
Cells(i + 3, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 4, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 4, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 4, 1).Font.Bold Then
Cells(i + 4, 1).Interior.ColorIndex = iNone
Cells(i + 4, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 5, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 5, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 5, 1).Font.Bold Then
Cells(i + 5, 1).Interior.ColorIndex = iNone
Cells(i + 5, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 6, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 6, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 6, 1).Font.Bold Then
Cells(i + 6, 1).Interior.ColorIndex = iNone
Cells(i + 6, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 7, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 7, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 7, 1).Font.Bold Then
Cells(i + 7, 1).Interior.ColorIndex = iNone
Cells(i + 7, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 8, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 8, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 8, 1).Font.Bold Then
Cells(i + 8, 1).Interior.ColorIndex = iNone
Cells(i + 8, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 9, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 9, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 9, 1).Font.Bold Then
Cells(i + 9, 1).Interior.ColorIndex = iNone
Cells(i + 9, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 10, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 10, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 10, 1).Font.Bold Then
Cells(i + 10, 1).Interior.ColorIndex = iNone
Cells(i + 10, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 11, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 11, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 11, 1).Font.Bold Then
Cells(i + 11, 1).Interior.ColorIndex = iNone
Cells(i + 11, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 12, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 12, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 12, 1).Font.Bold Then
Cells(i + 12, 1).Interior.ColorIndex = iNone
Cells(i + 12, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 13, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 13, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 13, 1).Font.Bold Then
Cells(i + 13, 1).Interior.ColorIndex = iNone
Cells(i + 13, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 14, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 14, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 14, 1).Font.Bold Then
Cells(i + 14, 1).Interior.ColorIndex = iNone
Cells(i + 14, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 15, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 15, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 15, 1).Font.Bold Then
Cells(i + 15, 1).Interior.ColorIndex = iNone
Cells(i + 15, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 16, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 16, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 16, 1).Font.Bold Then
Cells(i + 16, 1).Interior.ColorIndex = iNone
Cells(i + 16, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 17, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 17, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 17, 1).Font.Bold Then
Cells(i + 17, 1).Interior.ColorIndex = iNone
Cells(i + 17, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 18, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 18, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 18, 1).Font.Bold Then
Cells(i + 18, 1).Interior.ColorIndex = iNone
Cells(i + 18, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 19, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 19, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 19, 1).Font.Bold Then
Cells(i + 19, 1).Interior.ColorIndex = iNone
Cells(i + 19, 1).Font.Bold = False
End If

If Cells(i, 1) = Cells(i + 20, 1) And _
Cells(i, 1).Interior.ColorIndex = Cells(i + 20, 1).Interior.ColorIndex And _
Cells(i, 1).Font.Bold = Cells(i + 20, 1).Font.Bold Then
Cells(i + 20, 1).Interior.ColorIndex = iNone
Cells(i + 20, 1).Font.Bold = False
End If

Next i
Application.ScreenUpdating = True

End Sub
[\vba]

will anyone please try to reduce the above code please.

Thank you.

Thank you Mumps,

Well, I don't want every first number will be colored, I want, those first numbers, which the same second or third or etc. numbers were already colored. Please check my Post 1 attachment. My need mentioned from E2.

Thank you.

Hi friends,

I don't know the exact subject line.

With another vba, I am getting color in Column A based on another cell value, But I am getting color in multiple cells with the same number. I want to get color in the first cell (based on cell value) and remove all other cells color.
Please see attachment for better understanding.

Thank you.

Hi Friends,

Please help me with the solution.

Thank you.

Hi friends,

With below code, I can get red color in modified cells. After some time or days I will change red colored cells into no color, but that time previous color also removing. How can I keep my original color. Please check sample workbook.

[VBA]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

Target.Interior.ColorIndex = 3
Target.Font.Bold = True

exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/VBA]

Module:
[VBA]Sub Color()
Dim myRange As Range
Dim cell As Range
Set myRange = Range("A1:AU1200")
For Each cell In myRange
If cell.Interior.ColorIndex = 3 And _
cell.Font.Bold = True Then
cell.Interior.ColorIndex = 0
cell.Font.Bold = False

End If
Next
End Sub

[/VBA]

Thank you Friends,

Hi Friends,

Please help me with the solution.

Thank you.

yes

excelguru thread is closed, i can't post

Sorry AliGW, Here is my cross post links :

https://www.excelforum.com/exc…e-sheets.html#post4811051

https://www.excelguru.ca/forum…ction-for-Filter-userform

Please allow this post, once again sorry for this.

Sorry Trebor76 and all other persons for cross post. Just now I marked as unsubscribed that cross post, Please reply me.

Please see attachment.

Atuvally i have many columns , but I want only B, G, H, I, K, P, V, W, Z, AB, AD, AE columns in sheet 2

Thank you Trebor76

Thank you very much Trebor76, Its working good.

One small update please. In original sheet I have 50 + columns, but I want only few columns, how can I past perticular columns with range. Please update this small thing.

For example, Filter A column and past B, G, H, I, J, K, M, AB, AJ Columns.

Thank you.

Hi Trebor76,

Thank you for you reply.
With above sheet, I am getting only one value, If I selection in userform split data box 1 as "A", split data box 2 as "B", I am getting only B data, I want A & B data . Please see attachment.

Hi friends,

I am using this attached file to select multiple data to filter. But with this file I am getting multiple sheets. I want past into Sheet2 only. Please modified this.

Thank you.

Hi friends,

Using below code I am getting one cell information, how can I get multiple cells information.

Function FilterAndCopy(rng As Range, Choice As String)


'Clear Contents to show just new search data
Worksheets("Plant Sheet").Range("A12:BZ50").ClearContents
'Set the column to filter (In This Case 1 or A)
'Change as required


With Sheets("1. Data Entry")


rng.AutoFilter Field:=1, Criteria1:=Choice
Range("B1:B500").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Plant Sheet").Range("A13")
End With


On Error Resume Next
Sheets("1. Data Entry").ShowAllData
On Error GoTo 0




' Worksheets("Plant Sheet").Select
' Range("A12").Select
Call TurnAutoFilterOn
End Function




Option Explicit


Private Sub CommandButton1_Click()
Dim rng As Range


'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.ListObjects("Table112").Range
'Cancel if no value entered in textbox
If TextBox1.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, TextBox1.Value
rng.AutoFilter
'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub


Private Sub CommandButton2_Click()
'Cancel Button
Unload Me
End Sub


Private Sub UserForm_Click()


End Sub

Thank you Friends.