Posts by RAJESH SHAH

The query has been solved.

=SUMPRODUCT(($C$2:$C$23=$C2)*($G$2:$G$23>$G2-1)*($G$2:$G$23<$G2+1)*($H$2:$H$23>$H2-1)*($H$2:$H$23<$H2+1)*($I$2:$I$23>$I2-1)*($I$2:$I$23<$I2+1))>2

You are mistaken. I have shared all the 3 links where I have shared this post.

This is to inform you that the issue has not yet been resolved in any of the forums. If it is solved I will update this post without fail with the solution.

Conditional formatting with condition

Shared in these forums

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Hello guys
I have shared this query in more than one forum but I haven't received any solution.
Column C is the ID No. I am trying to find under each ID No. how many entries are of the same value in G to L. If the difference between the same value is more or less than 1.00 then it should accept as same value. If the row count is more than 2 then I want to color all the 3 rows using conditional formatting. I need your expertise to know what formula should I enter in the conditional formatting new rule.
I have colored the cells yellow to show the expected result.

Thank you in advance.

test CF.xlsx

link to one of the forums where I have tried to find a solution,

Conditional formatting with condition

Hello guys Column C is the ID No. I am trying to find under each ID No. how many entries are of the same value in G to L. If the difference between the same…

www.mrexcel.com

KjBox. Are you able to edit the formula accordingly. Something like NumberFormat = "0.00" within the formula

Gtg. Will be back in 1 hour. Please bear with me.

KjBox. The formula has taken the value from another sheet in the format in that sheet. My question is how do I edit the formula and get the result value in number format with 2 decimal places and if blank, it should not display any special characters in the cell.

All my files with amounts are in Indian format. I don't understand how it will help if I change to US format.

In the next sheet, when I try to add / subract the amounts in a different column, it shows as a value error, mainly becuase of the blank cells which have special characters in it. I even tried Ctrl G...... to select the blank cells but due to the special characters I am not able to select the blank cells and delete them.

But the result is in this format.

and therefore I am not able to format the column

Hello everyone, I have this formula in a cell which displays the result in General format. I would appreciate it if anyone can help me to edit the formula to get the result in a number format with 2 decimal places and if the result is blank I want the cell completely blank without any special characters in it. The formula is

=IF(VLOOKUP($A2,Sheet1!$A:$AZ,16,0)<>0,VLOOKUP($A2,Sheet1!$A:$AZ,16,0),IF(VLOOKUP($A2,Sheet1!$A:$AZ,32,0)<>0,VLOOKUP($A2,Sheet1!$A:$AZ,32,0),IF(VLOOKUP($A2,Sheet1!$A:$AZ,38,0)<>0,VLOOKUP($A2,Sheet1!$A:$AZ,38,0),"")))

FYI The problem is solved.

If I remove the Hidesheets line from the code, then the code runs correctly and generates an xml file. But the sheets are not hidden. What line of code should I add to the code to hide the sheets.?

Hello Roy. I am facing some issues. As you suggested, I have removed unprotection from the sheets.

When I run the first Macro Button it unhides the sheets but at the end of the code I need to hide the sheets again. In the code of the first macro, I have added a line below Sub() to unhide the sheets and at the end of the macro I have added another line to hide the sheets before End Sub. The error highlights the "Hidesheets." and displays - compile error Expected variable or procedure, not module.

Application.ScreenUpdating = False


  UnHideSheets


Application.ScreenUpdating = False


  HideSheets
I have altered the code to Hide and unhide sheets like this
Option Explicit

Sub HideSheets()
    Dim ws As Worksheet
    ThisWorkbook.Protect Password:=123
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Original" Then
        ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    
End Sub

Sub UnHideSheets()
    Dim ws As Worksheet
    ThisWorkbook.Unprotect Password:=123
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Original" Then
            ws.Visible = xlSheetVisible
            
        End If
    Next ws
    
End Sub

Hello experts

I need your expertise to find the fault in the code and solution to correct this. I have this workbook where the data is pasted in one sheet ”Original” from column A to J whose cells are not locked and not hidden. The rest of the sheet is protected with a password and the cells are hidden and locked. The macro button is in column K. Other than this sheet there are several other sheets which are protected and hidden. When I run the code, the code should unprotect and unhide all the protected and hidden sheets in the workbook. When the macro ends, I want the code to again protect and hide all the other sheets which were already hidden. The columns A:J in Original sheet must stay unlocked and unhidden at all times.

Option Explicit


Sub HideSheets()
Dim ws As Worksheet
ThisWorkbook.Unprotect Password:=123
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Original" Then
ws.Protect Password:=123
ws.Visible = xlSheetVeryHidden
Else
ws.Cells.Locked = True
ws.Range("A1:J" & Rows.Count).Locked = False
ws.Range("K:XFD").EntireColumn.Hidden = True
ws.Protect Password:=123
End If
Next ws
ThisWorkbook.Protect Password:=123
End Sub


Sub UnHideSheets()
Dim ws As Worksheet
ThisWorkbook.Unprotect Password:=123
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Original" Then
ws.Visible = xlSheetVisible
ws.Unprotect Password:=123
End If
Next ws
ThisWorkbook.Protect Password:=123
End Sub

Thank you so much Mumps. That was very kind of you. There is no end to learning in excel especially. So, whatever is related to accounts, I really try and learn the formulas and codes connected to it. Your words are encouraging and I need that inspiration. Thank you once again.

I have copied and saved the rules of this forum on my desktop. In future, before posting, I will make sure that the rules are followed.

I would like to know how to close my query once it is solved.

Quote from RAJESH SHAH

Mumps. Your code was right. Instead of button I changed it to the name of the button. That is why it was not accepting. So I recorded a macro and tried it your way and found it working. Thanks a lot. The problem now is solved.

</>ActiveSheet.Shapes.Range(Array("Button 3")).Select

ActiveSheet.Shapes("Button 3").ZOrder msoSendBackward

Range("K6").Select}

ActiveSheet.Shapes.Range(Array("Button 5")).Select

ActiveSheet.Shapes("Button 5").ZOrder msoSendBackward

Range("K7").Select</>

Display More

Mumps. Your code was right. Instead of button I changed it to the name of the button. That is why it was not accepting. So I recorded a macro and tried it your way and found it working. Thanks a lot. The problem now is solved.

{ActiveSheet.Shapes.Range(Array("Button 3")).Select

ActiveSheet.Shapes("Button 3").ZOrder msoSendBackward

Range("K6").Select}

ActiveSheet.Shapes.Range(Array("Button 5")).Select

ActiveSheet.Shapes("Button 5").ZOrder msoSendBackward

Range("K7").Select